{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:42.350609Z",
     "iopub.status.busy": "2023-11-07T03:39:42.350323Z",
     "iopub.status.idle": "2023-11-07T03:39:43.023906Z",
     "shell.execute_reply": "2023-11-07T03:39:43.023274Z",
     "shell.execute_reply.started": "2023-11-07T03:39:42.350573Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import os\n",
    "import time\n",
    "import gc\n",
    "from sklearn.model_selection import StratifiedKFold, KFold\n",
    "from sklearn.metrics import roc_auc_score,f1_score\n",
    "from sklearn.decomposition import TruncatedSVD\n",
    "from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer\n",
    "from scipy import sparse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 数据读取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.025268Z",
     "iopub.status.busy": "2023-11-07T03:39:43.025066Z",
     "iopub.status.idle": "2023-11-07T03:39:43.349257Z",
     "shell.execute_reply": "2023-11-07T03:39:43.348643Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.025244Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOG_train = pd.read_csv('../contest/train/EBANK_CSTLOG_QZ.csv') #网银金融性流水表\n",
    "CUST_FA_SUM_train = pd.read_csv('../contest/train/CUST_FA_SUM_QZ.csv')   #客户金融资产信息表\n",
    "NATURE_CUST_train = pd.read_csv('../contest/train/NATURE_CUST_QZ.csv')   #自然属性表\n",
    "TARGET_train = pd.read_csv('../contest/train/TARGET_QZ.csv')   #目标客户表\n",
    "DP_CUST_SUM_train =pd.read_csv('../contest/train/DP_CUST_SUM_QZ.csv')  #客户定活期存款信息表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.350767Z",
     "iopub.status.busy": "2023-11-07T03:39:43.350560Z",
     "iopub.status.idle": "2023-11-07T03:39:43.403936Z",
     "shell.execute_reply": "2023-11-07T03:39:43.403386Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.350743Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOG_A = pd.read_csv('../contest/A/EBANK_CSTLOG_QZ_A.csv') #网银金融性流水表\n",
    "CUST_FA_SUM_A = pd.read_csv('../contest/A/CUST_FA_SUM_QZ_A.csv')   #客户金融资产信息表\n",
    "NATURE_CUST_A = pd.read_csv('../contest/A/NATURE_CUST_QZ_A.csv')   #自然属性表\n",
    "TARGET_A = pd.read_csv('../contest/A/TARGET_QZ_A.csv')   #目标客户表\n",
    "DP_CUST_SUM_A=pd.read_csv('../contest/A/DP_CUST_SUM_QZ_A.csv')  #客户定活期存款信息表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2 数据探索"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.405139Z",
     "iopub.status.busy": "2023-11-07T03:39:43.404951Z",
     "iopub.status.idle": "2023-11-07T03:39:43.724063Z",
     "shell.execute_reply": "2023-11-07T03:39:43.723458Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.405117Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#定义描述性函数\n",
    "import seaborn as sns\n",
    "def stat_df(df):\n",
    "    stats = []\n",
    "    for col in df.columns:\n",
    "        stats.append((col, df[col].nunique(),df[col].isnull().sum()*100/df.shape[0],\n",
    "                     df[col].value_counts(normalize=True,dropna=False).values[0]*100,df[col].dtype))\n",
    "    stats_df = pd.DataFrame(stats, columns=['特征','唯一数数量','缺失值占比','最多数占比','类型'])\n",
    "    stats_df.sort_values('缺失值占比',ascending=False)\n",
    "    return stats_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 目标客户表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ① 训练集总客户数：37050  数据日期： 19940715\n",
    "\n",
    "### ② 验证集总客户数：5024   数据日期： 19940814"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:09:30.064996Z",
     "iopub.status.busy": "2023-10-23T07:09:30.064702Z",
     "iopub.status.idle": "2023-10-23T07:09:30.069052Z",
     "shell.execute_reply": "2023-10-23T07:09:30.068395Z",
     "shell.execute_reply.started": "2023-10-23T07:09:30.064968Z"
    },
    "tags": []
   },
   "source": [
    "## 2.2 网银金融性流水性表\n",
    "### ① 训练集：网银金融性流水性表，4月份的有17天，5月份的有25天，6月份的有14天\n",
    "\n",
    "### ② 测试集：网银金融性流水性表，5月份的有18天，6月份的有23天，7月份的有14天"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:19:27.608889Z",
     "iopub.status.busy": "2023-10-23T07:19:27.608592Z",
     "iopub.status.idle": "2023-10-23T07:19:27.611845Z",
     "shell.execute_reply": "2023-10-23T07:19:27.611216Z",
     "shell.execute_reply.started": "2023-10-23T07:19:27.608859Z"
    },
    "tags": []
   },
   "source": [
    "## 2.3 客户金融资产表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ① 训练集：数据日期-19940614\n",
    "\n",
    "### ② 测试集：数据日期-19940714\n",
    "\n",
    "由于此表日期唯一，无法进行滑窗，可考虑进行特征衍生"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:32:20.299772Z",
     "iopub.status.busy": "2023-10-23T07:32:20.299477Z",
     "iopub.status.idle": "2023-10-23T07:32:20.302684Z",
     "shell.execute_reply": "2023-10-23T07:32:20.302135Z",
     "shell.execute_reply.started": "2023-10-23T07:32:20.299742Z"
    },
    "tags": []
   },
   "source": [
    "## 2.4 自然属性表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ① 客户性别和客户等级可进行编码，无数据日期\n",
    "\n",
    "### ② 训练集：NTRL_CUST_SEX_CD 有少许缺失，测试集：暂无缺失"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 客户定活期存款信息表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ① 训练集测试集只有一个日期,客户数：36985\n",
    "\n",
    "### ② 测试集客户数：5019"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:37:34.419063Z",
     "iopub.status.busy": "2023-10-23T07:37:34.418759Z",
     "iopub.status.idle": "2023-10-23T07:37:34.421957Z",
     "shell.execute_reply": "2023-10-23T07:37:34.421416Z",
     "shell.execute_reply.started": "2023-10-23T07:37:34.419036Z"
    },
    "tags": []
   },
   "source": [
    "# 3 特征加工"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.725155Z",
     "iopub.status.busy": "2023-11-07T03:39:43.724953Z",
     "iopub.status.idle": "2023-11-07T03:39:43.739616Z",
     "shell.execute_reply": "2023-11-07T03:39:43.739096Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.725133Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集拼表处理\n",
    "target_data=pd.concat([TARGET_train, TARGET_A], axis = 0).reset_index(drop = True)\n",
    "#日期处理\n",
    "target_data['DATA_DAT'] = pd.to_datetime(target_data['DATA_DAT'], format='%Y%m%d')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1 自然信息表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.740594Z",
     "iopub.status.busy": "2023-11-07T03:39:43.740417Z",
     "iopub.status.idle": "2023-11-07T03:39:43.744151Z",
     "shell.execute_reply": "2023-11-07T03:39:43.743618Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.740572Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#定义one_hot编码函数\n",
    "def one_hot(df, cate_cols):\n",
    "    df_dummies = pd.get_dummies(df.loc[:,[cate_cols]])\n",
    "    return pd.concat([df,df_dummies],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.745458Z",
     "iopub.status.busy": "2023-11-07T03:39:43.745272Z",
     "iopub.status.idle": "2023-11-07T03:39:43.775004Z",
     "shell.execute_reply": "2023-11-07T03:39:43.774500Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.745436Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集合并处理\n",
    "nature_cust_data = pd.concat([NATURE_CUST_train, NATURE_CUST_A], axis = 0).reset_index(drop = True) \n",
    "# 对【NTRL_RANK_CD】进行one_hot 编码，并删除原特征\n",
    "nature_cust_data=one_hot(nature_cust_data,'NTRL_RANK_CD')\n",
    "del nature_cust_data['NTRL_RANK_CD']\n",
    "#对性别进行编码\n",
    "sex_dict = {'A': 0, 'B': 1}\n",
    "nature_cust_data['NTRL_CUST_SEX_CD'] = nature_cust_data['NTRL_CUST_SEX_CD'].map(sex_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.775939Z",
     "iopub.status.busy": "2023-11-07T03:39:43.775763Z",
     "iopub.status.idle": "2023-11-07T03:39:43.795656Z",
     "shell.execute_reply": "2023-11-07T03:39:43.795148Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.775917Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_9_nature.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(nature_cust_data, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 金融资产表(金融资产表和定活期)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:43.945831Z",
     "iopub.status.busy": "2023-11-07T03:39:43.945613Z",
     "iopub.status.idle": "2023-11-07T03:39:44.135699Z",
     "shell.execute_reply": "2023-11-07T03:39:44.135178Z",
     "shell.execute_reply.started": "2023-11-07T03:39:43.945807Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集合并处理\n",
    "cust_fa_sum_data =  pd.concat([CUST_FA_SUM_train, CUST_FA_SUM_A], axis = 0).reset_index(drop = True) \n",
    "dp_cust_sum_data = pd.concat([DP_CUST_SUM_train,DP_CUST_SUM_A],axis = 0).reset_index(drop = True)\n",
    "\n",
    "#和目标表拼接处理\n",
    "asset_data=target_data.merge(cust_fa_sum_data, on = 'CUST_NO', how = 'left')\n",
    "asset_data=asset_data.merge(dp_cust_sum_data, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 特征衍生"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:44.154051Z",
     "iopub.status.busy": "2023-11-07T03:39:44.153851Z",
     "iopub.status.idle": "2023-11-07T03:39:44.262503Z",
     "shell.execute_reply": "2023-11-07T03:39:44.261987Z",
     "shell.execute_reply.started": "2023-11-07T03:39:44.154018Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "asset_data['aum_y']=(asset_data['DAY_AUM_BAL']-(asset_data['YAVER_AUM_BAL'])/asset_data['DAY_AUM_BAL'])\n",
    "asset_data['aum_s']=(asset_data['DAY_AUM_BAL']-(asset_data['SAVER_AUM_BAL'])/asset_data['DAY_AUM_BAL'])\n",
    "asset_data['aum_m']=(asset_data['DAY_AUM_BAL']-(asset_data['YAVER_AUM_BAL'])/asset_data['DAY_AUM_BAL'])\n",
    "\n",
    "asset_data['DPSA_y']=(asset_data['DPSA_BAL']-(asset_data['YAVER_DPSA_BAL'])/asset_data['DPSA_BAL'])\n",
    "asset_data['DPSA_s']=(asset_data['DPSA_BAL']-(asset_data['SAVER_DPSA_BAL'])/asset_data['DPSA_BAL'])\n",
    "asset_data['DPSA_m']=(asset_data['DPSA_BAL']-(asset_data['MAVER_DPSA_BAL'])/asset_data['DPSA_BAL'])\n",
    "\n",
    "#特征衍生1：AUM/金融资产（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_AUM_FA']=asset_data['DAY_AUM_BAL']/(asset_data['DAY_FA_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_AUM_FA']=asset_data['MAVER_AUM_BAL']/(asset_data['MAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_AUM_FA']=asset_data['SAVER_AUM_BAL']/(asset_data['SAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_AUM_FA']=asset_data['YAVER_AUM_BAL']/(asset_data['YAVER_FA_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生2：活期存款/aum（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_huoqi_aum']=asset_data['DPSA_BAL']/(asset_data['DAY_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_huoqi_aum']=asset_data['MAVER_DPSA_BAL']/(asset_data['MAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_huoqi_aum']=asset_data['SAVER_DPSA_BAL']/(asset_data['SAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_huoqi_aum']=asset_data['YAVER_DPSA_BAL']/(asset_data['YAVER_AUM_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生3：定期存款/aum（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_dingqi_aum']=asset_data['TD_BAL']/(asset_data['DAY_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_dingqi_aum']=asset_data['MAVER_TD_BAL']/(asset_data['MAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_dingqi_aum']=asset_data['SAVER_TD_BAL']/(asset_data['SAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_dingqi_aum']=asset_data['YAVER_TD_BAL']/(asset_data['YAVER_AUM_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生4：金融资产/总投资（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_fa_touzi']=asset_data['DAY_FA_BAL']/(asset_data['TOT_IVST_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_fa_touzi']=asset_data['MAVER_FA_BAL']/(asset_data['MAVER_TOT_IVST_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_fa_touzi']=asset_data['SAVER_FA_BAL']/(asset_data['SAVER_TOT_IVST_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_fa_touzi']=asset_data['YAVER_FA_BAL']/(asset_data['YAVER_TOT_IVST_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生5：fa-aum(贷款)/金融资产（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_daikuan_fa']=(asset_data['DAY_FA_BAL']-asset_data['DAY_AUM_BAL'])/(asset_data['DAY_FA_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_daikuan_fa']=(asset_data['MAVER_FA_BAL']-asset_data['MAVER_AUM_BAL'])/(asset_data['MAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_daikuan_fa']=(asset_data['SAVER_FA_BAL']-asset_data['SAVER_AUM_BAL'])/(asset_data['SAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_daikuan_fa']=(asset_data['YAVER_FA_BAL']-asset_data['YAVER_AUM_BAL'])/(asset_data['YAVER_FA_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生6：活期+定期/aum（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_huoding_aum']=(asset_data['DPSA_BAL']+asset_data['TD_BAL'])/(asset_data['DAY_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_huoding_aum']=(asset_data['MAVER_DPSA_BAL']+asset_data['MAVER_TD_BAL'])/(asset_data['MAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_huoding_aum']=(asset_data['SAVER_DPSA_BAL']+asset_data['SAVER_TD_BAL'])/(asset_data['SAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_huoding_aum']=(asset_data['YAVER_DPSA_BAL']+asset_data['YAVER_TD_BAL'])/(asset_data['YAVER_AUM_BAL']+ 0.00000000001)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:44.587587Z",
     "iopub.status.busy": "2023-11-07T03:39:44.587367Z",
     "iopub.status.idle": "2023-11-07T03:39:44.599631Z",
     "shell.execute_reply": "2023-11-07T03:39:44.599180Z",
     "shell.execute_reply.started": "2023-11-07T03:39:44.587559Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42074, 51)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#去除日期等特征\n",
    "col=['DATA_DAT_x','CARD_NO','DATA_DAT_y','FLAG','DATA_DAT']\n",
    "for i in col:\n",
    "    del asset_data[i]\n",
    "asset_data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:44.787451Z",
     "iopub.status.busy": "2023-11-07T03:39:44.787255Z",
     "iopub.status.idle": "2023-11-07T03:39:44.849362Z",
     "shell.execute_reply": "2023-11-07T03:39:44.848882Z",
     "shell.execute_reply.started": "2023-11-07T03:39:44.787429Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_6&&7_asset.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(asset_data, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 掌银金融性流水表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 通用函数定义"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:44.989904Z",
     "iopub.status.busy": "2023-11-07T03:39:44.989694Z",
     "iopub.status.idle": "2023-11-07T03:39:44.996162Z",
     "shell.execute_reply": "2023-11-07T03:39:44.995680Z",
     "shell.execute_reply.started": "2023-11-07T03:39:44.989879Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Groupby\n",
    "def make_gp(df, pk, agg_col=None, agg_actions=None, agg_dict=None, extra_col=None):\n",
    "    if agg_dict is not None:\n",
    "        pass\n",
    "    elif (agg_col is not None) and (agg_actions is not None):\n",
    "        agg_dict = dict.fromkeys(agg_col)\n",
    "        for key in agg_dict:\n",
    "            agg_dict[key] = agg_actions\n",
    "    else:\n",
    "        raise ValueError(\"请输入要进行Groupby的列和操作\")\n",
    "    \n",
    "    if extra_col is not None:\n",
    "        gp = df.groupby(pk)[extra_col].agg(agg_dict)\n",
    "    else:\n",
    "        gp = df.groupby(pk).agg(agg_dict)\n",
    "    \n",
    "    gp.columns = ['_'.join(col).strip() for col in gp.columns.values]\n",
    "    gp.reset_index(inplace=True)\n",
    "    return gp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:45.425853Z",
     "iopub.status.busy": "2023-11-07T03:39:45.425657Z",
     "iopub.status.idle": "2023-11-07T03:39:45.429427Z",
     "shell.execute_reply": "2023-11-07T03:39:45.428951Z",
     "shell.execute_reply.started": "2023-11-07T03:39:45.425825Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#重新命名字段段名\n",
    "def rename_df(df, group_id,ext=\"\"):\n",
    "    new_cols = [group_id]\n",
    "    for i in df.columns:\n",
    "        if i!=group_id:\n",
    "            new_cols.append(ext+\"_\"+i)\n",
    "    return new_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:45.629468Z",
     "iopub.status.busy": "2023-11-07T03:39:45.629260Z",
     "iopub.status.idle": "2023-11-07T03:39:56.695614Z",
     "shell.execute_reply": "2023-11-07T03:39:56.694991Z",
     "shell.execute_reply.started": "2023-11-07T03:39:45.629445Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集拼表处理\n",
    "MBANK_TRNFLW_train = pd.read_csv('../contest/train/MBANK_TRNFLW_QZ.csv') #掌银金融性流水表\n",
    "MBANK_TRNFLW_A = pd.read_csv('../contest/A/MBANK_TRNFLW_QZ_A.csv') \n",
    "\n",
    "MBANK_TRNFLW_data=pd.DataFrame()\n",
    "MBANK_TRNFLW_data = pd.concat([MBANK_TRNFLW_train, MBANK_TRNFLW_A], axis = 0).reset_index(drop = True)\n",
    "\n",
    "#年月日维度转换\n",
    "MBANK_TRNFLW_data['TFT_DTE_TIME'] = pd.to_datetime(MBANK_TRNFLW_data['TFT_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "MBANK_TRNFLW_data['TFT_DTE_year'] = MBANK_TRNFLW_data['TFT_DTE_TIME'].dt.year\n",
    "MBANK_TRNFLW_data['TFT_DTE_month'] = MBANK_TRNFLW_data['TFT_DTE_TIME'].dt.month\n",
    "MBANK_TRNFLW_data['TFT_DTE_day'] = MBANK_TRNFLW_data['TFT_DTE_TIME'].dt.day\n",
    "\n",
    "#统一客户号字段名\n",
    "MBANK_TRNFLW_data=MBANK_TRNFLW_data.rename(columns={\"TFT_CSTNO\":\"CUST_NO\"})\n",
    "\n",
    "#拼表\n",
    "tr_MBANK_TRNFLW=target_data.merge(MBANK_TRNFLW_data,on=\"CUST_NO\",how=\"left\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:39:56.696940Z",
     "iopub.status.busy": "2023-11-07T03:39:56.696734Z",
     "iopub.status.idle": "2023-11-07T03:40:31.193762Z",
     "shell.execute_reply": "2023-11-07T03:40:31.193159Z",
     "shell.execute_reply.started": "2023-11-07T03:39:56.696916Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#计算时间差\n",
    "def quzheng(col1, col2):\n",
    "    return (col1-col2) \n",
    "\n",
    "tr_MBANK_TRNFLW[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_MBANK_TRNFLW[\"DATA_DAT\"],tr_MBANK_TRNFLW[\"TFT_DTE_TIME\"]))\n",
    "tr_MBANK_TRNFLW[\"time_diff\"]=tr_MBANK_TRNFLW[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_MBANK_TRNFLW=tr_MBANK_TRNFLW[tr_MBANK_TRNFLW[\"time_diff\"]>=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:31.195323Z",
     "iopub.status.busy": "2023-11-07T03:40:31.195119Z",
     "iopub.status.idle": "2023-11-07T03:40:31.269567Z",
     "shell.execute_reply": "2023-11-07T03:40:31.269017Z",
     "shell.execute_reply.started": "2023-11-07T03:40:31.195298Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "col=['DATA_DAT','TFT_DTE_TIME','TFT_DTE_year']\n",
    "for i in col:\n",
    "    del tr_MBANK_TRNFLW[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-24T02:22:07.606994Z",
     "iopub.status.busy": "2023-10-24T02:22:07.606700Z",
     "iopub.status.idle": "2023-10-24T02:22:07.610075Z",
     "shell.execute_reply": "2023-10-24T02:22:07.609501Z",
     "shell.execute_reply.started": "2023-10-24T02:22:07.606966Z"
    },
    "tags": []
   },
   "source": [
    "### 3.3.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:31.270896Z",
     "iopub.status.busy": "2023-11-07T03:40:31.270705Z",
     "iopub.status.idle": "2023-11-07T03:42:02.563977Z",
     "shell.execute_reply": "2023-11-07T03:42:02.563478Z",
     "shell.execute_reply.started": "2023-11-07T03:40:31.270873Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41217, 49)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "tmqlw_1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "tmqlw_1_time_diff = make_gp(tmqlw_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "tmqlw_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "tmqlw_1_time_diff_1 = make_gp(tmqlw_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "tmqlw_1_time_diff_1.columns = rename_df(tmqlw_1_time_diff_1,'CUST_NO',\"trnflw_custno\")\n",
    "\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "tmqlw_1_month = make_gp(tmqlw_1, ['CUST_NO', 'TFT_DTE_month'], ['CUST_NO'], ['count'])\n",
    "tmqlw_1_month.columns = ['CUST_NO', 'TFT_DTE_month', 'montimes']\n",
    "tmqlw_1_month_1 =  make_gp(tmqlw_1_month, ['CUST_NO'], ['TFT_DTE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "tmqlw_1_month_1.columns = rename_df(tmqlw_1_month_1,'CUST_NO',\"trnflw_mon\")\n",
    "\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "tmqlw_1_day= make_gp(tmqlw_1, ['CUST_NO', 'TFT_DTE_day'], ['CUST_NO'], ['count'])\n",
    "tmqlw_1_day.columns = ['CUST_NO', 'TFT_DTE_day', 'daytimes']\n",
    "tmqlw_1_day_1 = make_gp(tmqlw_1_day, ['CUST_NO'], ['TFT_DTE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "tmqlw_1_day_1.columns = rename_df(tmqlw_1_day_1,'CUST_NO',\"trnflw_day\")\n",
    "\n",
    "#频数汇总整理\n",
    "tmqlw_1_huizong = pd.DataFrame()\n",
    "tmqlw_1_huizong= tmqlw_1_time_diff_1.merge(tmqlw_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_1_huizong= tmqlw_1_huizong.merge(tmqlw_1_day_1, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "tmqlw_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.2 交易金额分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:42:02.565080Z",
     "iopub.status.busy": "2023-11-07T03:42:02.564885Z",
     "iopub.status.idle": "2023-11-07T03:43:26.355563Z",
     "shell.execute_reply": "2023-11-07T03:43:26.355065Z",
     "shell.execute_reply.started": "2023-11-07T03:42:02.565058Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41217, 36)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计总交易金额\n",
    "trnflw_am1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_am1_1 = make_gp(trnflw_am1,  ['CUST_NO'], ['TFT_TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_am1_1.columns = rename_df(trnflw_am1_1,'CUST_NO',\"trnflw_cnoAMT\")\n",
    "\n",
    "#根据【客户号,月】统计交易金额\n",
    "trnflw_am1_m = make_gp(trnflw_am1,  ['CUST_NO'], ['TFT_DTE_month', 'TFT_TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_am1_m.columns = rename_df(trnflw_am1_m,'CUST_NO',\"trnflw_mAMT\")\n",
    "\n",
    "#根据【客户号，天】统计交易金额\n",
    "trnflw_am1_d= make_gp(trnflw_am1,  ['CUST_NO'], ['TFT_DTE_day', 'TFT_TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_am1_d.columns = rename_df(trnflw_am1_d,'CUST_NO',\"trnflw_dAMT\")\n",
    "\n",
    "#交易金额汇总整理\n",
    "tmqlw_am1_huizong = pd.DataFrame()\n",
    "tmqlw_am1_huizong= trnflw_am1_1.merge(trnflw_am1_m, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_am1_huizong= tmqlw_am1_huizong.merge(trnflw_am1_d, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "tmqlw_am1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.3 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:43:26.356672Z",
     "iopub.status.busy": "2023-11-07T03:43:26.356480Z",
     "iopub.status.idle": "2023-11-07T03:43:30.984147Z",
     "shell.execute_reply": "2023-11-07T03:43:30.983602Z",
     "shell.execute_reply.started": "2023-11-07T03:43:26.356648Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41217, 11)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计交易代码\n",
    "trnflw_cod1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_cod1_1 = make_gp(trnflw_cod1,  ['CUST_NO'], ['TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "trnflw_cod1_1.columns = rename_df(trnflw_cod1_1,'CUST_NO',\"trnflw_cod\")\n",
    "\n",
    "#根据【客户号,月】统计交易代码\n",
    "trnflw_cod1_m = make_gp(trnflw_cod1,  ['CUST_NO'], ['TFT_DTE_month','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "trnflw_cod1_m.columns = rename_df(trnflw_cod1_m,'CUST_NO',\"trnflw_mcod\")\n",
    "\n",
    "#根据【客户号，天】统计交易代码\n",
    "trnflw_cod1_d = make_gp(trnflw_cod1,  ['CUST_NO'], ['TFT_DTE_day','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "trnflw_cod1_d.columns = rename_df(trnflw_cod1_d,'CUST_NO',\"trnflw_dcod\")\n",
    "\n",
    "#交易代码汇总整理\n",
    "tmqlw_cod1_huizong = pd.DataFrame()\n",
    "tmqlw_cod1_huizong= trnflw_cod1_1.merge(trnflw_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_cod1_huizong= tmqlw_cod1_huizong.merge(trnflw_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.4 交易对手账号分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:43:30.985281Z",
     "iopub.status.busy": "2023-11-07T03:43:30.985084Z",
     "iopub.status.idle": "2023-11-07T03:43:35.638232Z",
     "shell.execute_reply": "2023-11-07T03:43:35.637740Z",
     "shell.execute_reply.started": "2023-11-07T03:43:30.985258Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41217, 11)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计交易对手\n",
    "trnflw_acc1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_acc1_1 = make_gp(trnflw_acc1,  ['CUST_NO'], ['TFT_CSTACC'],['count', 'nunique'])\n",
    "trnflw_acc1_1.columns = rename_df(trnflw_acc1_1,'CUST_NO',\"trnflw_acc\")\n",
    "\n",
    "#根据【客户号，月】统计交易对手\n",
    "trnflw_acc1_m = make_gp(trnflw_acc1,  ['CUST_NO'], ['TFT_DTE_month','TFT_CSTACC'],['count', 'nunique'])\n",
    "trnflw_acc1_m.columns = rename_df(trnflw_acc1_m,'CUST_NO',\"trnflw_macc\")\n",
    "\n",
    "#根据【客户号，天】统计交易对手\n",
    "trnflw_acc1_d = make_gp(trnflw_acc1,  ['CUST_NO'], ['TFT_DTE_day','TFT_CSTACC'],['count', 'nunique'])\n",
    "trnflw_acc1_d.columns = rename_df(trnflw_acc1_d,'CUST_NO',\"trnflw_dacc\")\n",
    "\n",
    "#交易对手汇总整理\n",
    "trnflw_acc1_huizong = pd.DataFrame()\n",
    "trnflw_acc1_huizong= trnflw_acc1_1.merge(trnflw_acc1_m, on = 'CUST_NO', how = 'left')\n",
    "trnflw_acc1_huizong= trnflw_acc1_huizong.merge(trnflw_acc1_d, on = 'CUST_NO', how = 'left')\n",
    "trnflw_acc1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.5 汇总1-4分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:43:35.640189Z",
     "iopub.status.busy": "2023-11-07T03:43:35.639973Z",
     "iopub.status.idle": "2023-11-07T03:43:36.165366Z",
     "shell.execute_reply": "2023-11-07T03:43:36.164822Z",
     "shell.execute_reply.started": "2023-11-07T03:43:35.640165Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41217, 104)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trnflw_feature=pd.DataFrame(MBANK_TRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "trnflw_feature=trnflw_feature.merge(tmqlw_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "trnflw_feature=trnflw_feature.merge(tmqlw_am1_huizong, on = 'CUST_NO', how = 'left')  \n",
    "trnflw_feature=trnflw_feature.merge(tmqlw_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "trnflw_feature=trnflw_feature.merge(trnflw_acc1_huizong, on = 'CUST_NO', how = 'left') \n",
    "trnflw_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.6 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:43:36.167164Z",
     "iopub.status.busy": "2023-11-07T03:43:36.166926Z",
     "iopub.status.idle": "2023-11-07T03:45:37.730813Z",
     "shell.execute_reply": "2023-11-07T03:45:37.730225Z",
     "shell.execute_reply.started": "2023-11-07T03:43:36.167138Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39651, 65)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "trnflw_2 = tmqlw_1_time_diff.copy(deep=True) \n",
    "\n",
    "trnflw_2_2 = trnflw_2.loc[trnflw_2['time_diff']>42]\n",
    "trnflw_2_2 = make_gp(trnflw_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_2.columns = ['CUST_NO'] + [f +'_trnflw'+'_last14' for f in trnflw_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_2_4 = trnflw_2.loc[trnflw_2['time_diff']>28]\n",
    "trnflw_2_4 = make_gp(trnflw_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_4.columns = ['CUST_NO'] + [f +'_trnflw'+ '_last28' for f in trnflw_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_2_6 = trnflw_2.loc[trnflw_2['time_diff']>14]\n",
    "trnflw_2_6 = make_gp(trnflw_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_6.columns = ['CUST_NO'] + [f +'_trnflw'+ '_last42' for f in trnflw_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_2_8 = trnflw_2.loc[trnflw_2['time_diff']>0]\n",
    "trnflw_2_8 = make_gp(trnflw_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_8.columns = ['CUST_NO'] + [f +'_trnflw'+ '_last56' for f in trnflw_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "trnflw_2_huizong=pd.DataFrame()\n",
    "trnflw_2_huizong=trnflw_2_2.merge(trnflw_2_4, on = 'CUST_NO', how = 'left')\n",
    "trnflw_2_huizong=trnflw_2_huizong.merge(trnflw_2_6, on = 'CUST_NO', how = 'left')\n",
    "trnflw_2_huizong=trnflw_2_huizong.merge(trnflw_2_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "#和总量作对比\n",
    "for d in [14, 28,42,56]:\n",
    "        trnflw_2_huizong[f'times_trnflw_rate_last{d}'] = trnflw_2_huizong[f'times_sum_trnflw_last{d}'] / tmqlw_1_huizong[f'trnflw_custno_times_sum']\n",
    "        trnflw_2_huizong[f'time_diff_trnflw_rate_last{d}'] = trnflw_2_huizong[f'time_diff_sum_trnflw_last{d}'] / tmqlw_1_huizong[f'trnflw_custno_time_diff_sum']\n",
    "#查看shape\n",
    "trnflw_2_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.7 交易金额滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:45:37.731914Z",
     "iopub.status.busy": "2023-11-07T03:45:37.731717Z",
     "iopub.status.idle": "2023-11-07T03:45:42.114925Z",
     "shell.execute_reply": "2023-11-07T03:45:42.114282Z",
     "shell.execute_reply.started": "2023-11-07T03:45:37.731890Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "trnflw_3 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_3.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "trnflw_3_2 = make_gp(trnflw_3[(trnflw_3['time_diff']>42)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_2.columns = ['CUST_NO'] + [f + '_trnflwam'+'_last14' for f in trnflw_3_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_3_4 = make_gp(trnflw_3[(trnflw_3['time_diff']>28)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_4.columns = ['CUST_NO'] + [f + '_trnflwam'+'_last28' for f in trnflw_3_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_3_6 = make_gp(trnflw_3[(trnflw_3['time_diff']>14)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_6.columns = ['CUST_NO'] + [f + '_trnflwam'+ '_last42' for f in trnflw_3_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_3_8 = make_gp(trnflw_3[(trnflw_3['time_diff']>0)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_8.columns = ['CUST_NO'] + [f + '_trnflwam' + '_last56' for f in trnflw_3_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "trnflw_3_huizong=pd.DataFrame()\n",
    "trnflw_3_huizong=trnflw_3_2.merge(trnflw_3_4, on = 'CUST_NO', how = 'left')\n",
    "trnflw_3_huizong=trnflw_3_huizong.merge(trnflw_3_6, on = 'CUST_NO', how = 'left')\n",
    "trnflw_3_huizong=trnflw_3_huizong.merge(trnflw_3_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "###滑窗后的交易金额占比：（总——分）/分\n",
    "for col2 in ['TFT_TRNAMT', ]:\n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last28']                                                                                               \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last28']                                                                                                \n",
    "    \n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last28']                                                                                         \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last28']\n",
    "    \n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last42'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last42']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last42'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last42']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last42']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last42']                                                                                                    \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last42']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last42']\n",
    "    \n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last56'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last56']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last56'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last56']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last56']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last56']                                                                                             \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last56']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last56']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.8 汇总6-7的滑窗结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:45:42.115985Z",
     "iopub.status.busy": "2023-11-07T03:45:42.115796Z",
     "iopub.status.idle": "2023-11-07T03:45:42.463186Z",
     "shell.execute_reply": "2023-11-07T03:45:42.462685Z",
     "shell.execute_reply.started": "2023-11-07T03:45:42.115961Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41217, 196)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trnflw_feature=trnflw_feature.merge(trnflw_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "trnflw_feature=trnflw_feature.merge(trnflw_3_huizong, on = 'CUST_NO', how = 'left') \n",
    "trnflw_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.9 保存trnflw_feature特征结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:45:42.464287Z",
     "iopub.status.busy": "2023-11-07T03:45:42.464096Z",
     "iopub.status.idle": "2023-11-07T03:45:42.646874Z",
     "shell.execute_reply": "2023-11-07T03:45:42.646308Z",
     "shell.execute_reply.started": "2023-11-07T03:45:42.464264Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_1_trnflw.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(trnflw_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.4 掌银非金融流水表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:45:42.648016Z",
     "iopub.status.busy": "2023-11-07T03:45:42.647817Z",
     "iopub.status.idle": "2023-11-07T03:49:58.864268Z",
     "shell.execute_reply": "2023-11-07T03:49:58.863653Z",
     "shell.execute_reply.started": "2023-11-07T03:45:42.647993Z"
    }
   },
   "outputs": [],
   "source": [
    "#读表\n",
    "MBANK_QRYTRNFLW_train=pd.read_csv('../contest/train/MBANK_QRYTRNFLW_QZ.csv')  #掌银非金融流水表\n",
    "MBANK_QRYTRNFLW_A  =pd.read_csv('../contest/A/MBANK_QRYTRNFLW_QZ_A.csv') \n",
    "MBANK_QRYTRNFLW_data=pd.DataFrame()\n",
    "MBANK_QRYTRNFLW_data = pd.concat([MBANK_QRYTRNFLW_train, MBANK_QRYTRNFLW_A], axis = 0).reset_index(drop = True)\n",
    "# 年月日维度转换\n",
    "MBANK_QRYTRNFLW_data['TFT_DTE_TIME'] = pd.to_datetime(MBANK_QRYTRNFLW_data['TFT_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "MBANK_QRYTRNFLW_data['TFT_DTE_year'] = MBANK_QRYTRNFLW_data['TFT_DTE_TIME'].dt.year\n",
    "MBANK_QRYTRNFLW_data['TFT_DTE_month'] = MBANK_QRYTRNFLW_data['TFT_DTE_TIME'].dt.month\n",
    "MBANK_QRYTRNFLW_data['TFT_DTE_day'] = MBANK_QRYTRNFLW_data['TFT_DTE_TIME'].dt.day\n",
    "#统一客户号字段名\n",
    "MBANK_QRYTRNFLW_data=MBANK_QRYTRNFLW_data.rename(columns={\"TFT_CSTNO\":\"CUST_NO\"})\n",
    "#拼表\n",
    "tr_MBANK_QRYTRNFLW=target_data.merge(MBANK_QRYTRNFLW_data,on=\"CUST_NO\",how=\"left\")\n",
    "#日期作差\n",
    "tr_MBANK_QRYTRNFLW[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_MBANK_QRYTRNFLW[\"DATA_DAT\"],tr_MBANK_QRYTRNFLW[\"TFT_DTE_TIME\"]))\n",
    "tr_MBANK_QRYTRNFLW[\"time_diff\"]=tr_MBANK_QRYTRNFLW[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_MBANK_QRYTRNFLW=tr_MBANK_QRYTRNFLW[tr_MBANK_QRYTRNFLW[\"time_diff\"]>=0]\n",
    "\n",
    "#去除日期字段\n",
    "col=['DATA_DAT','TFT_DTE_TIME','TFT_DTE_year']\n",
    "for i in col:\n",
    "    del tr_MBANK_QRYTRNFLW[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:49:58.865478Z",
     "iopub.status.busy": "2023-11-07T03:49:58.865269Z",
     "iopub.status.idle": "2023-11-07T03:51:32.708725Z",
     "shell.execute_reply": "2023-11-07T03:51:32.708227Z",
     "shell.execute_reply.started": "2023-11-07T03:49:58.865454Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41882, 49)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "QRYTRNFLW_1 = tr_MBANK_QRYTRNFLW.copy(deep=True)\n",
    "QRYTRNFLW_1_time_diff = make_gp(QRYTRNFLW_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "QRYTRNFLW_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "QRYTRNFLW_1_time_diff_1 = make_gp(QRYTRNFLW_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_1_time_diff_1.columns = rename_df(QRYTRNFLW_1_time_diff_1,'CUST_NO',\"QRYTRNFLW\")\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "QRYTRNFLW_1_month = make_gp(QRYTRNFLW_1, ['CUST_NO', 'TFT_DTE_month'], ['CUST_NO'], ['count'])\n",
    "QRYTRNFLW_1_month.columns = ['CUST_NO', 'TFT_DTE_month', 'montimes']\n",
    "QRYTRNFLW_1_month_1 =  make_gp(QRYTRNFLW_1_month, ['CUST_NO'], ['TFT_DTE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_1_month_1.columns = rename_df(QRYTRNFLW_1_month_1,'CUST_NO',\"QRYTRNFLW_m\")\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "QRYTRNFLW_1_day= make_gp(QRYTRNFLW_1, ['CUST_NO', 'TFT_DTE_day'], ['CUST_NO'], ['count'])\n",
    "QRYTRNFLW_1_day.columns = ['CUST_NO', 'TFT_DTE_day', 'daytimes']\n",
    "QRYTRNFLW_1_day_1 = make_gp(QRYTRNFLW_1_day, ['CUST_NO'], ['TFT_DTE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_1_day_1.columns = rename_df(QRYTRNFLW_1_day_1,'CUST_NO',\"QRYTRNFLW_d\")\n",
    "\n",
    "#汇总整理\n",
    "QRYTRNFLW_1_huizong = pd.DataFrame()\n",
    "QRYTRNFLW_1_huizong= QRYTRNFLW_1_time_diff_1.merge(QRYTRNFLW_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_1_huizong= QRYTRNFLW_1_huizong.merge(QRYTRNFLW_1_day_1, on = 'CUST_NO', how = 'left')    \n",
    "QRYTRNFLW_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.2 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:51:32.709804Z",
     "iopub.status.busy": "2023-11-07T03:51:32.709611Z",
     "iopub.status.idle": "2023-11-07T03:51:53.827851Z",
     "shell.execute_reply": "2023-11-07T03:51:53.827362Z",
     "shell.execute_reply.started": "2023-11-07T03:51:32.709781Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41882, 11)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计交易代码\n",
    "QRYTRNFLW_cod1 = tr_MBANK_QRYTRNFLW.copy(deep=True)\n",
    "QRYTRNFLW_cod1_1 = make_gp(QRYTRNFLW_cod1,  ['CUST_NO'], ['TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "QRYTRNFLW_cod1_1.columns = rename_df(QRYTRNFLW_cod1_1,'CUST_NO',\"QRYTRNFLW_cod\")\n",
    "#根据【客户号，月】统计交易代码\n",
    "QRYTRNFLW_cod1_m = make_gp(QRYTRNFLW_cod1,  ['CUST_NO'], ['TFT_DTE_month','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "QRYTRNFLW_cod1_m.columns = rename_df(QRYTRNFLW_cod1_m,'CUST_NO',\"QRYTRNFLW_mcod\")\n",
    "#根据【客户号，日】统计交易代码\n",
    "QRYTRNFLW_cod1_d = make_gp(QRYTRNFLW_cod1,  ['CUST_NO'], ['TFT_DTE_day','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "QRYTRNFLW_cod1_d.columns = rename_df(QRYTRNFLW_cod1_d,'CUST_NO',\"QRYTRNFLW_dcod\")\n",
    "#汇总整理\n",
    "QRYTRNFLW_cod1_huizong = pd.DataFrame()\n",
    "QRYTRNFLW_cod1_huizong  =QRYTRNFLW_cod1_1.merge(QRYTRNFLW_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_cod1_huizong  =QRYTRNFLW_cod1_huizong.merge(QRYTRNFLW_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.3 汇总1-2分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:51:53.828960Z",
     "iopub.status.busy": "2023-11-07T03:51:53.828764Z",
     "iopub.status.idle": "2023-11-07T03:51:54.854424Z",
     "shell.execute_reply": "2023-11-07T03:51:54.853929Z",
     "shell.execute_reply.started": "2023-11-07T03:51:53.828936Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41882, 59)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "QRYTRNFLW_feature=pd.DataFrame(MBANK_QRYTRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "QRYTRNFLW_feature = QRYTRNFLW_feature.merge(QRYTRNFLW_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature = QRYTRNFLW_feature.merge(QRYTRNFLW_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.4 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:51:54.855541Z",
     "iopub.status.busy": "2023-11-07T03:51:54.855343Z",
     "iopub.status.idle": "2023-11-07T03:53:48.786066Z",
     "shell.execute_reply": "2023-11-07T03:53:48.785426Z",
     "shell.execute_reply.started": "2023-11-07T03:51:54.855517Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "QRYTRNFLW_2 = QRYTRNFLW_1_time_diff.copy(deep=True) \n",
    "\n",
    "QRYTRNFLW_2_2 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>42]\n",
    "QRYTRNFLW_2_2 = make_gp(QRYTRNFLW_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_2.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last14' for f in QRYTRNFLW_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "QRYTRNFLW_2_4 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>28]\n",
    "QRYTRNFLW_2_4 = make_gp(QRYTRNFLW_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_4.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last28' for f in QRYTRNFLW_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "QRYTRNFLW_2_6 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>14]\n",
    "QRYTRNFLW_2_6 = make_gp(QRYTRNFLW_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_6.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last42' for f in QRYTRNFLW_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "QRYTRNFLW_2_8 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>0]\n",
    "QRYTRNFLW_2_8 = make_gp(QRYTRNFLW_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_8.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last56' for f in QRYTRNFLW_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "QRYTRNFLW_2_huizong=pd.DataFrame()\n",
    "QRYTRNFLW_2_huizong=QRYTRNFLW_2_2.merge(QRYTRNFLW_2_4, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_2_huizong=QRYTRNFLW_2_huizong.merge(QRYTRNFLW_2_6, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_2_huizong=QRYTRNFLW_2_huizong.merge(QRYTRNFLW_2_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "#和总量作对比\n",
    "for d in [14, 28,42,56]:\n",
    "        QRYTRNFLW_2_huizong[f'times_QRYTRNFLW_rate_last{d}'] = QRYTRNFLW_2_huizong[f'times_sum_QRYTRNFLW_last{d}'] / QRYTRNFLW_1_huizong[f'QRYTRNFLW_times_sum']\n",
    "        QRYTRNFLW_2_huizong[f'time_QRYTRNFLW_diff_rate_last{d}'] = QRYTRNFLW_2_huizong[f'time_diff_sum_QRYTRNFLW_last{d}'] / QRYTRNFLW_1_huizong[f'QRYTRNFLW_time_diff_sum']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.5  汇总所有结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:48.787147Z",
     "iopub.status.busy": "2023-11-07T03:53:48.786942Z",
     "iopub.status.idle": "2023-11-07T03:53:49.005126Z",
     "shell.execute_reply": "2023-11-07T03:53:49.004630Z",
     "shell.execute_reply.started": "2023-11-07T03:53:48.787122Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41882, 123)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "QRYTRNFLW_feature=QRYTRNFLW_feature.merge(QRYTRNFLW_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.6 保存QRYTRNFLW_feature 特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:49.006221Z",
     "iopub.status.busy": "2023-11-07T03:53:49.006016Z",
     "iopub.status.idle": "2023-11-07T03:53:49.130406Z",
     "shell.execute_reply": "2023-11-07T03:53:49.129851Z",
     "shell.execute_reply.started": "2023-11-07T03:53:49.006196Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_2_QRYTRNFLW.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(QRYTRNFLW_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.5 网银金融性流水表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:49.131488Z",
     "iopub.status.busy": "2023-11-07T03:53:49.131297Z",
     "iopub.status.idle": "2023-11-07T03:53:49.771180Z",
     "shell.execute_reply": "2023-11-07T03:53:49.770591Z",
     "shell.execute_reply.started": "2023-11-07T03:53:49.131465Z"
    }
   },
   "outputs": [],
   "source": [
    "#读表\n",
    "EBANK_CSTLOG_train = pd.read_csv('../contest/train/EBANK_CSTLOG_QZ.csv') #网银金融性流水表\n",
    "EBANK_CSTLOG_A = pd.read_csv('../contest/A/EBANK_CSTLOG_QZ_A.csv') \n",
    "EBANK_CSTLOG_data=pd.DataFrame()\n",
    "EBANK_CSTLOG_data = pd.concat([EBANK_CSTLOG_train, EBANK_CSTLOG_A], axis = 0).reset_index(drop = True)\n",
    "# 年月日维度转换\n",
    "EBANK_CSTLOG_data['ADDFIELDDATE'] = pd.to_datetime(EBANK_CSTLOG_data['ADDFIELDDATE'], format='%Y%m%d')\n",
    "EBANK_CSTLOG_data['ADDFIELDDATE_year'] = EBANK_CSTLOG_data['ADDFIELDDATE'].dt.year\n",
    "EBANK_CSTLOG_data['ADDFIELDDATE_month'] = EBANK_CSTLOG_data['ADDFIELDDATE'].dt.month\n",
    "EBANK_CSTLOG_data['ADDFIELDDATE_day'] = EBANK_CSTLOG_data['ADDFIELDDATE'].dt.day\n",
    "#统一客户号字段名\n",
    "EBANK_CSTLOG_data=EBANK_CSTLOG_data.rename(columns={\"CSTNO\":\"CUST_NO\"})\n",
    "#拼表\n",
    "tr_EBANK_CSTLOG=target_data.merge(EBANK_CSTLOG_data,on=\"CUST_NO\",how=\"left\")\n",
    "tr_EBANK_CSTLOG[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_EBANK_CSTLOG[\"DATA_DAT\"],tr_EBANK_CSTLOG[\"ADDFIELDDATE\"]))\n",
    "tr_EBANK_CSTLOG[\"time_diff\"]=tr_EBANK_CSTLOG[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_EBANK_CSTLOG=tr_EBANK_CSTLOG[tr_EBANK_CSTLOG[\"time_diff\"]>=0]\n",
    "col=['DATA_DAT','ADDFIELDDATE','ADDFIELDDATE_year']\n",
    "for i in col:\n",
    "    del tr_EBANK_CSTLOG[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:49.772364Z",
     "iopub.status.busy": "2023-11-07T03:53:49.772160Z",
     "iopub.status.idle": "2023-11-07T03:53:50.827806Z",
     "shell.execute_reply": "2023-11-07T03:53:50.827326Z",
     "shell.execute_reply.started": "2023-11-07T03:53:49.772340Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493, 49)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "CSTLOG_1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_1_time_diff = make_gp(CSTLOG_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "CSTLOG_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "CSTLOG_1_time_diff_1 = make_gp(CSTLOG_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_1_time_diff_1.columns = rename_df(CSTLOG_1_time_diff_1,'CUST_NO',\"CSTLOG\")\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "CSTLOG_1_month = make_gp(CSTLOG_1, ['CUST_NO', 'ADDFIELDDATE_month'], ['CUST_NO'], ['count'])\n",
    "CSTLOG_1_month.columns = ['CUST_NO', 'ADDFIELDDATE_month', 'montimes']\n",
    "CSTLOG_1_month_1 =  make_gp(CSTLOG_1_month, ['CUST_NO'], ['ADDFIELDDATE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_1_month_1.columns = rename_df(CSTLOG_1_month_1,'CUST_NO',\"CSTLOG_m\")\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "CSTLOG_1_day= make_gp(CSTLOG_1, ['CUST_NO', 'ADDFIELDDATE_day'], ['CUST_NO'], ['count'])\n",
    "CSTLOG_1_day.columns = ['CUST_NO', 'ADDFIELDDATE_day', 'daytimes']\n",
    "CSTLOG_1_day_1 = make_gp(CSTLOG_1_day, ['CUST_NO'], ['ADDFIELDDATE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_1_day_1.columns = rename_df(CSTLOG_1_day_1,'CUST_NO',\"CSTLOG_d\")\n",
    "#汇总整理\n",
    "CSTLOG_1_huizong = pd.DataFrame()\n",
    "CSTLOG_1_huizong= CSTLOG_1_time_diff_1.merge(CSTLOG_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_1_huizong= CSTLOG_1_huizong.merge(CSTLOG_1_day_1, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.2 交易金额分组统计\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:50.828859Z",
     "iopub.status.busy": "2023-11-07T03:53:50.828674Z",
     "iopub.status.idle": "2023-11-07T03:53:51.727936Z",
     "shell.execute_reply": "2023-11-07T03:53:51.727460Z",
     "shell.execute_reply.started": "2023-11-07T03:53:50.828836Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493, 36)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计交易金额\n",
    "CSTLOG_am1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_am1_1 = make_gp(CSTLOG_am1,  ['CUST_NO'], ['TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_am1_1.columns = rename_df(CSTLOG_am1_1,'CUST_NO',\"CSTLOG_am\")\n",
    "#根据【客户号，月】统计交易金额\n",
    "CSTLOG_am1_m = make_gp(CSTLOG_am1,  ['CUST_NO'], ['ADDFIELDDATE_month', 'TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_am1_m.columns = rename_df(CSTLOG_am1_m,'CUST_NO',\"CSTLOG_mam\")\n",
    "#根据【客户号，日】统计交易金额\n",
    "CSTLOG_am1_d= make_gp(CSTLOG_am1,  ['CUST_NO'], ['ADDFIELDDATE_day', 'TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_am1_d.columns = rename_df(CSTLOG_am1_d,'CUST_NO',\"CSTLOG_dam\")\n",
    "#汇总整理\n",
    "CSTLOG_am1_huizong = pd.DataFrame()\n",
    "CSTLOG_am1_huizong= CSTLOG_am1_1.merge(CSTLOG_am1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_am1_huizong= CSTLOG_am1_huizong.merge(CSTLOG_am1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_am1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.3 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:51.728989Z",
     "iopub.status.busy": "2023-11-07T03:53:51.728809Z",
     "iopub.status.idle": "2023-11-07T03:53:51.778215Z",
     "shell.execute_reply": "2023-11-07T03:53:51.777760Z",
     "shell.execute_reply.started": "2023-11-07T03:53:51.728967Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493, 11)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计交易代码\n",
    "CSTLOG_cod1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_cod1_1 = make_gp(CSTLOG_cod1,  ['CUST_NO'], ['BSNCODE'],['count', 'nunique'])\n",
    "CSTLOG_cod1_1.columns = rename_df(CSTLOG_cod1_1,'CUST_NO',\"CSTLOG_cod\")\n",
    "#根据【客户号，月】统计交易代码\n",
    "CSTLOG_cod1_m = make_gp(CSTLOG_cod1,  ['CUST_NO'], ['ADDFIELDDATE_month','BSNCODE'],['count', 'nunique'])\n",
    "CSTLOG_cod1_m.columns = rename_df(CSTLOG_cod1_m,'CUST_NO',\"CSTLOG_mcod\")\n",
    "#根据【客户号，天】统计交易代码\n",
    "CSTLOG_cod1_d = make_gp(CSTLOG_cod1,  ['CUST_NO'], ['ADDFIELDDATE_day','BSNCODE'],['count', 'nunique'])\n",
    "CSTLOG_cod1_d.columns = rename_df(CSTLOG_cod1_d,'CUST_NO',\"CSTLOG_dcod\")\n",
    "\n",
    "#汇总整理\n",
    "CSTLOG_cod1_huizong = pd.DataFrame()\n",
    "CSTLOG_cod1_huizong= CSTLOG_cod1_1.merge(CSTLOG_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_cod1_huizong= CSTLOG_cod1_huizong.merge(CSTLOG_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.4 交易对手账号分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:51.779174Z",
     "iopub.status.busy": "2023-11-07T03:53:51.778988Z",
     "iopub.status.idle": "2023-11-07T03:53:51.827605Z",
     "shell.execute_reply": "2023-11-07T03:53:51.827170Z",
     "shell.execute_reply.started": "2023-11-07T03:53:51.779151Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493, 11)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】统计交易对手账号\n",
    "CSTLOG_acc1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_acc1_1 = make_gp(CSTLOG_acc1,  ['CUST_NO'], ['FRMACCTNO'],['count', 'nunique'])\n",
    "CSTLOG_acc1_1.columns = rename_df(CSTLOG_acc1_1,'CUST_NO',\"CSTLOG_acc\")\n",
    "#根据【客户号，月】统计交易对手账号\n",
    "CSTLOG_acc1_m = make_gp(CSTLOG_acc1,  ['CUST_NO'], ['ADDFIELDDATE_month','FRMACCTNO'],['count', 'nunique'])\n",
    "CSTLOG_acc1_m.columns = rename_df(CSTLOG_acc1_m,'CUST_NO',\"CSTLOG_macc\")\n",
    "#根据【客户号，天】统计交易对手账号\n",
    "CSTLOG_acc1_d = make_gp(CSTLOG_acc1,  ['CUST_NO'], ['ADDFIELDDATE_day','FRMACCTNO'],['count', 'nunique'])\n",
    "CSTLOG_acc1_d.columns = rename_df(CSTLOG_acc1_d,'CUST_NO',\"CSTLOG_dacc\")\n",
    "\n",
    "#汇总整理\n",
    "CSTLOG_acc1_huizong = pd.DataFrame()\n",
    "CSTLOG_acc1_huizong= CSTLOG_acc1_1.merge(CSTLOG_acc1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_acc1_huizong= CSTLOG_acc1_huizong.merge(CSTLOG_acc1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_acc1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.5 汇总1-4分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:51.828479Z",
     "iopub.status.busy": "2023-11-07T03:53:51.828313Z",
     "iopub.status.idle": "2023-11-07T03:53:51.856728Z",
     "shell.execute_reply": "2023-11-07T03:53:51.856287Z",
     "shell.execute_reply.started": "2023-11-07T03:53:51.828459Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493, 104)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOG_feature=pd.DataFrame(EBANK_CSTLOG_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_am1_huizong, on = 'CUST_NO', how = 'left')  \n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_acc1_huizong, on = 'CUST_NO', how = 'left') \n",
    "CSTLOG_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.6 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:51.857626Z",
     "iopub.status.busy": "2023-11-07T03:53:51.857459Z",
     "iopub.status.idle": "2023-11-07T03:53:53.178508Z",
     "shell.execute_reply": "2023-11-07T03:53:53.177965Z",
     "shell.execute_reply.started": "2023-11-07T03:53:51.857606Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(401, 65)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "CSTLOG_2 = CSTLOG_1_time_diff.copy(deep=True) \n",
    "\n",
    "CSTLOG_2_2 = CSTLOG_2.loc[CSTLOG_2['time_diff']>42]\n",
    "CSTLOG_2_2 = make_gp(CSTLOG_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_2.columns = ['CUST_NO'] + [f +'_CSTLOG'+ '_last14' for f in CSTLOG_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_2_4 = CSTLOG_2.loc[CSTLOG_2['time_diff']>28]\n",
    "CSTLOG_2_4 = make_gp(CSTLOG_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_4.columns = ['CUST_NO'] + [f +'_CSTLOG' + '_last28' for f in CSTLOG_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_2_6 = CSTLOG_2.loc[CSTLOG_2['time_diff']>14]\n",
    "CSTLOG_2_6 = make_gp(CSTLOG_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_6.columns = ['CUST_NO'] + [f +'_CSTLOG'+ '_last42' for f in CSTLOG_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_2_8 = CSTLOG_2.loc[CSTLOG_2['time_diff']>0]\n",
    "CSTLOG_2_8 = make_gp(CSTLOG_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_8.columns = ['CUST_NO'] + [f +'_CSTLOG'+ '_last56' for f in CSTLOG_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总\n",
    "CSTLOG_2_huizong=pd.DataFrame()\n",
    "CSTLOG_2_huizong=CSTLOG_2_2.merge(CSTLOG_2_4, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_2_huizong=CSTLOG_2_huizong.merge(CSTLOG_2_6, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_2_huizong=CSTLOG_2_huizong.merge(CSTLOG_2_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "#和总量作对比\n",
    "for d in [14, 28,42,56]:\n",
    "        CSTLOG_2_huizong[f'times_CSTLOG_rate_last{d}'] = CSTLOG_2_huizong[f'times_sum_CSTLOG_last{d}'] / CSTLOG_1_huizong[f'CSTLOG_times_sum']\n",
    "        CSTLOG_2_huizong[f'time_CSTLOG_diff_rate_last{d}'] = CSTLOG_2_huizong[f'time_diff_sum_CSTLOG_last{d}'] / CSTLOG_1_huizong[f'CSTLOG_time_diff_sum']\n",
    "\n",
    "CSTLOG_2_huizong.shape\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.7 交易金额滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:53.179514Z",
     "iopub.status.busy": "2023-11-07T03:53:53.179328Z",
     "iopub.status.idle": "2023-11-07T03:53:53.279551Z",
     "shell.execute_reply": "2023-11-07T03:53:53.278956Z",
     "shell.execute_reply.started": "2023-11-07T03:53:53.179492Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "CSTLOG_3 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_3.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "CSTLOG_3_2 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>42)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_2.columns = ['CUST_NO'] + [f +'_CSTLOGamt'+ '_last14' for f in CSTLOG_3_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_3_4 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>28)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_4.columns = ['CUST_NO'] + [f +'_CSTLOGamt'+ '_last28' for f in CSTLOG_3_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_3_6 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>14)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_6.columns = ['CUST_NO'] + [f +'_CSTLOGamt' + '_last42' for f in CSTLOG_3_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_3_8 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>0)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_8.columns = ['CUST_NO'] + [f +'_CSTLOGamt'+ '_last56' for f in CSTLOG_3_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "CSTLOG_3_huizong=pd.DataFrame()\n",
    "CSTLOG_3_huizong=CSTLOG_3_2.merge(CSTLOG_3_4, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_3_huizong=CSTLOG_3_huizong.merge(CSTLOG_3_6, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_3_huizong=CSTLOG_3_huizong.merge(CSTLOG_3_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "#滑窗后的交易金额占比：（总—分）/总\n",
    "for col2 in ['TRNAMT', ]:\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28']                                                                                                        \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28'])/ CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28']                                                                                                          \n",
    "    \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28']                                                                                                     \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28']\n",
    "    \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last42'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last42']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last42'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last42']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last42']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last42']                                                                                                       \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last42']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last42']\n",
    "    \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last56'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last56']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last56'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last56']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last56']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last56']                                                                                         \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last56']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last56']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.8 汇总所有的滑窗结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:53.280510Z",
     "iopub.status.busy": "2023-11-07T03:53:53.280331Z",
     "iopub.status.idle": "2023-11-07T03:53:53.303895Z",
     "shell.execute_reply": "2023-11-07T03:53:53.303450Z",
     "shell.execute_reply.started": "2023-11-07T03:53:53.280488Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493, 196)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_3_huizong, on = 'CUST_NO', how = 'left') \n",
    "CSTLOG_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.9 保存CSTLOG_feature特征结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:53.304837Z",
     "iopub.status.busy": "2023-11-07T03:53:53.304664Z",
     "iopub.status.idle": "2023-11-07T03:53:53.313897Z",
     "shell.execute_reply": "2023-11-07T03:53:53.313407Z",
     "shell.execute_reply.started": "2023-11-07T03:53:53.304816Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_3_CSTLOG.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(CSTLOG_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.6 网银非金融性流水表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:53.314896Z",
     "iopub.status.busy": "2023-11-07T03:53:53.314709Z",
     "iopub.status.idle": "2023-11-07T03:55:39.770831Z",
     "shell.execute_reply": "2023-11-07T03:55:39.770229Z",
     "shell.execute_reply.started": "2023-11-07T03:53:53.314873Z"
    }
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOGQUERY_train=pd.read_csv('../contest/train/EBANK_CSTLOGQUERY_QZ.csv')  #网银非金融流水表\n",
    "EBANK_CSTLOGQUERY_A  =pd.read_csv('../contest/A/EBANK_CSTLOGQUERY_QZ_A.csv') \n",
    "EBANK_CSTLOGQUERY_data=pd.DataFrame()\n",
    "EBANK_CSTLOGQUERY_data = pd.concat([EBANK_CSTLOGQUERY_train, EBANK_CSTLOGQUERY_A], axis = 0).reset_index(drop = True)\n",
    "# 年月日维度转换\n",
    "EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'] = pd.to_datetime(EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "EBANK_CSTLOGQUERY_data['CLQ_DTE_year'] = EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'].dt.year\n",
    "EBANK_CSTLOGQUERY_data['CLQ_DTE_month'] = EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'].dt.month\n",
    "EBANK_CSTLOGQUERY_data['CLQ_DTE_day'] = EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'].dt.day\n",
    "#统一客户号字段名\n",
    "EBANK_CSTLOGQUERY_data=EBANK_CSTLOGQUERY_data.rename(columns={\"CLQ_CSTNO\":\"CUST_NO\"})\n",
    "#拼表\n",
    "tr_EBANK_CSTLOGQUERY=target_data.merge(EBANK_CSTLOGQUERY_data,on=\"CUST_NO\",how=\"left\")\n",
    "tr_EBANK_CSTLOGQUERY[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_EBANK_CSTLOGQUERY[\"DATA_DAT\"],tr_EBANK_CSTLOGQUERY[\"CLQ_DTE_TIME\"]))\n",
    "tr_EBANK_CSTLOGQUERY[\"time_diff\"]=tr_EBANK_CSTLOGQUERY[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_EBANK_CSTLOGQUERY=tr_EBANK_CSTLOGQUERY[tr_EBANK_CSTLOGQUERY[\"time_diff\"]>=0]\n",
    "\n",
    "col=['DATA_DAT','CLQ_DTE_TIME','CLQ_DTE_year']\n",
    "for i in col:\n",
    "    del tr_EBANK_CSTLOGQUERY[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:39.771996Z",
     "iopub.status.busy": "2023-11-07T03:55:39.771798Z",
     "iopub.status.idle": "2023-11-07T03:55:45.260298Z",
     "shell.execute_reply": "2023-11-07T03:55:45.259808Z",
     "shell.execute_reply.started": "2023-11-07T03:55:39.771972Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1083, 49)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "CSTLOGQUERY_1 = tr_EBANK_CSTLOGQUERY.copy(deep=True)\n",
    "CSTLOGQUERY_1_time_diff = make_gp(CSTLOGQUERY_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "CSTLOGQUERY_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "CSTLOGQUERY_1_time_diff_1 = make_gp(CSTLOGQUERY_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_1_time_diff_1.columns = rename_df(CSTLOGQUERY_1_time_diff_1,'CUST_NO',\"CSTLOGQUERY\")\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "CSTLOGQUERY_1_month = make_gp(CSTLOGQUERY_1, ['CUST_NO', 'CLQ_DTE_month'], ['CUST_NO'], ['count'])\n",
    "CSTLOGQUERY_1_month.columns = ['CUST_NO', 'CLQ_DTE_month', 'montimes']\n",
    "CSTLOGQUERY_1_month_1 =  make_gp(CSTLOGQUERY_1_month, ['CUST_NO'], ['CLQ_DTE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_1_month_1.columns = rename_df(CSTLOGQUERY_1_month_1,'CUST_NO',\"CSTLOGQUERY_m\")\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "CSTLOGQUERY_1_day= make_gp(CSTLOGQUERY_1, ['CUST_NO', 'CLQ_DTE_day'], ['CUST_NO'], ['count'])\n",
    "CSTLOGQUERY_1_day.columns = ['CUST_NO', 'CLQ_DTE_day', 'daytimes']\n",
    "CSTLOGQUERY_1_day_1 = make_gp(CSTLOGQUERY_1_day, ['CUST_NO'], ['CLQ_DTE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_1_day_1.columns = rename_df(CSTLOGQUERY_1_day_1,'CUST_NO',\"CSTLOGQUERY_d\")\n",
    "\n",
    "#汇总整理\n",
    "CSTLOGQUERY_1_huizong = pd.DataFrame()\n",
    "CSTLOGQUERY_1_huizong= CSTLOGQUERY_1_time_diff_1.merge(CSTLOGQUERY_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_1_huizong= CSTLOGQUERY_1_huizong.merge(CSTLOGQUERY_1_day_1, on = 'CUST_NO', how = 'left')  \n",
    "CSTLOGQUERY_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.2 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:45.261370Z",
     "iopub.status.busy": "2023-11-07T03:55:45.261181Z",
     "iopub.status.idle": "2023-11-07T03:55:52.949523Z",
     "shell.execute_reply": "2023-11-07T03:55:52.949002Z",
     "shell.execute_reply.started": "2023-11-07T03:55:45.261346Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1083, 11)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据【客户号】交易代码统计\n",
    "CSTLOGQUERY_cod1 = tr_EBANK_CSTLOGQUERY.copy(deep=True)\n",
    "CSTLOGQUERY_cod1_1 = make_gp(CSTLOGQUERY_cod1,  ['CUST_NO'], ['CLQ_BSNCOD'],['count', 'nunique'])\n",
    "CSTLOGQUERY_cod1_1.columns = rename_df(CSTLOGQUERY_cod1_1,'CUST_NO',\"CSTLOGQUERY_cod\")\n",
    "#根据【客户号，月】统计交易代码\n",
    "CSTLOGQUERY_cod1_m = make_gp(CSTLOGQUERY_cod1,  ['CUST_NO'], ['CLQ_DTE_month','CLQ_BSNCOD'],['count', 'nunique'])\n",
    "CSTLOGQUERY_cod1_m.columns = rename_df(CSTLOGQUERY_cod1_m,'CUST_NO',\"CSTLOGQUERY_mcod\")\n",
    "#根据【客户号，日】统计交易代码\n",
    "CSTLOGQUERY_cod1_d = make_gp(CSTLOGQUERY_cod1,  ['CUST_NO'], ['CLQ_DTE_day','CLQ_BSNCOD'],['count', 'nunique'])\n",
    "CSTLOGQUERY_cod1_d.columns = rename_df(CSTLOGQUERY_cod1_d,'CUST_NO',\"CSTLOGQUERY_dcod\")\n",
    "#汇总整理\n",
    "CSTLOGQUERY_cod1_huizong = pd.DataFrame()\n",
    "CSTLOGQUERY_cod1_huizong  =CSTLOGQUERY_cod1_1.merge(CSTLOGQUERY_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_cod1_huizong  =CSTLOGQUERY_cod1_huizong.merge(CSTLOGQUERY_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.3 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:52.950618Z",
     "iopub.status.busy": "2023-11-07T03:55:52.950415Z",
     "iopub.status.idle": "2023-11-07T03:55:53.127163Z",
     "shell.execute_reply": "2023-11-07T03:55:53.126695Z",
     "shell.execute_reply.started": "2023-11-07T03:55:52.950594Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1083, 59)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOGQUERY_feature=pd.DataFrame(EBANK_CSTLOGQUERY_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOGQUERY_feature = CSTLOGQUERY_feature.merge(CSTLOGQUERY_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature = CSTLOGQUERY_feature.merge(CSTLOGQUERY_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.4 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:53.128201Z",
     "iopub.status.busy": "2023-11-07T03:55:53.128005Z",
     "iopub.status.idle": "2023-11-07T03:55:55.962511Z",
     "shell.execute_reply": "2023-11-07T03:55:55.961875Z",
     "shell.execute_reply.started": "2023-11-07T03:55:53.128178Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "CSTLOGQUERY_2 = CSTLOGQUERY_1_time_diff.copy(deep=True) \n",
    "\n",
    "CSTLOGQUERY_2_2 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>42]\n",
    "CSTLOGQUERY_2_2 = make_gp(CSTLOGQUERY_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_2.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last14' for f in CSTLOGQUERY_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOGQUERY_2_4 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>28]\n",
    "CSTLOGQUERY_2_4 = make_gp(CSTLOGQUERY_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_4.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last28' for f in CSTLOGQUERY_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOGQUERY_2_6 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>14]\n",
    "CSTLOGQUERY_2_6 = make_gp(CSTLOGQUERY_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_6.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last42' for f in CSTLOGQUERY_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOGQUERY_2_8 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>0]\n",
    "CSTLOGQUERY_2_8 = make_gp(CSTLOGQUERY_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_8.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last56' for f in CSTLOGQUERY_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "CSTLOGQUERY_2_huizong=pd.DataFrame()\n",
    "CSTLOGQUERY_2_huizong=CSTLOGQUERY_2_2.merge(CSTLOGQUERY_2_4, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_2_huizong=CSTLOGQUERY_2_huizong.merge(CSTLOGQUERY_2_6, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_2_huizong=CSTLOGQUERY_2_huizong.merge(CSTLOGQUERY_2_8, on = 'CUST_NO', how = 'left')\n",
    "##作比\n",
    "for d in [14, 28,42,56]:\n",
    "        CSTLOGQUERY_2_huizong[f'times_CSTLOGQUERY_rate_last{d}'] = CSTLOGQUERY_2_huizong[f'times_sum_CSTLOGQUERY_last{d}'] / CSTLOGQUERY_1_huizong[f'CSTLOGQUERY_times_sum']\n",
    "        CSTLOGQUERY_2_huizong[f'time_CSTLOGQUERY_diff_rate_last{d}'] = CSTLOGQUERY_2_huizong[f'time_diff_sum_CSTLOGQUERY_last{d}'] / CSTLOGQUERY_1_huizong[f'CSTLOGQUERY_time_diff_sum']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.5 汇总所有结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:55.963551Z",
     "iopub.status.busy": "2023-11-07T03:55:55.963359Z",
     "iopub.status.idle": "2023-11-07T03:55:55.979087Z",
     "shell.execute_reply": "2023-11-07T03:55:55.978630Z",
     "shell.execute_reply.started": "2023-11-07T03:55:55.963528Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1083, 123)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOGQUERY_feature=CSTLOGQUERY_feature.merge(CSTLOGQUERY_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.6 保存CSTLOGQUERY_feature 特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:55.980043Z",
     "iopub.status.busy": "2023-11-07T03:55:55.979862Z",
     "iopub.status.idle": "2023-11-07T03:55:55.989997Z",
     "shell.execute_reply": "2023-11-07T03:55:55.989530Z",
     "shell.execute_reply.started": "2023-11-07T03:55:55.980014Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_4_CSTLOGQUERY.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(CSTLOGQUERY_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.7 借记卡交易流水表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:55.990928Z",
     "iopub.status.busy": "2023-11-07T03:55:55.990751Z",
     "iopub.status.idle": "2023-11-07T03:59:37.198927Z",
     "shell.execute_reply": "2023-11-07T03:59:37.198257Z",
     "shell.execute_reply.started": "2023-11-07T03:55:55.990906Z"
    }
   },
   "outputs": [],
   "source": [
    "#读表\n",
    "APS_train = pd.read_csv('../contest/train/APS_QZ.csv') #借记卡流水表\n",
    "APS_A = pd.read_csv('../contest/A/APS_QZ_A.csv') \n",
    "APS_data=pd.DataFrame()\n",
    "APS_data = pd.concat([APS_train, APS_A], axis = 0).reset_index(drop = True)\n",
    "# 年月日维度转换\n",
    "APS_data['APSDTRDAT_TM'] = pd.to_datetime(APS_data['APSDTRDAT_TM'], format='%Y%m%d%H%M%S')\n",
    "APS_data['APSDTRDAT_TM_year'] = APS_data['APSDTRDAT_TM'].dt.year\n",
    "APS_data['APSDTRDAT_TM_month'] = APS_data['APSDTRDAT_TM'].dt.month\n",
    "APS_data['APSDTRDAT_TM_day'] = APS_data['APSDTRDAT_TM'].dt.day\n",
    "#统一卡号字段名\n",
    "APS_data=APS_data.rename(columns={\"APSDPRDNO\":\"CARD_NO\"})\n",
    "#拼表\n",
    "tr_APS=target_data.merge(APS_data,on=\"CARD_NO\",how=\"left\")\n",
    "tr_APS[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_APS[\"DATA_DAT\"],tr_APS[\"APSDTRDAT_TM\"]))\n",
    "tr_APS[\"time_diff\"]=tr_APS[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_APS=tr_APS[tr_APS[\"time_diff\"]>=0]\n",
    "\n",
    "col=['DATA_DAT','APSDTRDAT_TM','APSDTRDAT_TM_year']\n",
    "for i in col:\n",
    "    del tr_APS[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:59:37.200131Z",
     "iopub.status.busy": "2023-11-07T03:59:37.199922Z",
     "iopub.status.idle": "2023-11-07T04:01:10.424662Z",
     "shell.execute_reply": "2023-11-07T04:01:10.423994Z",
     "shell.execute_reply.started": "2023-11-07T03:59:37.200106Z"
    }
   },
   "outputs": [],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "APS_1 = tr_APS.copy(deep=True)\n",
    "APS_1_time_diff = make_gp(APS_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "APS_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "APS_1_time_diff_1 = make_gp(APS_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_1_time_diff_1.columns = rename_df(APS_1_time_diff_1,'CUST_NO',\"APS\")\n",
    "#根据APSDTRDAT_TM_month统计交易频数\n",
    "APS_1_month = make_gp(APS_1, ['CUST_NO', 'APSDTRDAT_TM_month'], ['CUST_NO'], ['count'])\n",
    "APS_1_month.columns = ['CUST_NO', 'APSDTRDAT_TM_month', 'montimes']\n",
    "APS_1_month_1 =  make_gp(APS_1_month, ['CUST_NO'], ['APSDTRDAT_TM_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_1_month_1.columns = rename_df(APS_1_month_1,'CUST_NO',\"APS_m\")\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "APS_1_day= make_gp(APS_1, ['CUST_NO', 'APSDTRDAT_TM_day'], ['CUST_NO'], ['count'])\n",
    "APS_1_day.columns = ['CUST_NO', 'APSDTRDAT_TM_day', 'daytimes']\n",
    "APS_1_day_1 = make_gp(APS_1_day, ['CUST_NO'], ['APSDTRDAT_TM_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_1_day_1.columns = rename_df(APS_1_day_1,'CUST_NO',\"APS_d\")\n",
    "#汇总整理\n",
    "APS_1_huizong = pd.DataFrame()\n",
    "APS_1_huizong= APS_1_time_diff_1.merge(APS_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "APS_1_huizong= APS_1_huizong.merge(APS_1_day_1, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.2 转出金额分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:01:10.425889Z",
     "iopub.status.busy": "2023-11-07T04:01:10.425669Z",
     "iopub.status.idle": "2023-11-07T04:02:32.111587Z",
     "shell.execute_reply": "2023-11-07T04:02:32.110924Z",
     "shell.execute_reply.started": "2023-11-07T04:01:10.425864Z"
    }
   },
   "outputs": [],
   "source": [
    "#根据【客户号】统计\n",
    "APS_am1 = tr_APS[tr_APS['APSDTRAMT']<0].copy(deep=True)\n",
    "APS_am1_1 = make_gp(APS_am1,  ['CUST_NO'], ['APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am1_1.columns = rename_df(APS_am1_1,'CUST_NO',\"APS_outamt\")\n",
    "#根据【客户号，月】分组统计\n",
    "APS_am1_m = make_gp(APS_am1,  ['CUST_NO'], ['APSDTRDAT_TM_month', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am1_m.columns = rename_df(APS_am1_m,'CUST_NO',\"APS_moutamt\")\n",
    "#根据【客户号，天】分组统计\n",
    "APS_am1_d= make_gp(APS_am1,  ['CUST_NO'], ['APSDTRDAT_TM_day', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am1_d.columns = rename_df(APS_am1_d,'CUST_NO',\"APS_doutamt\")\n",
    "\n",
    "#汇总整理\n",
    "APS_am1_out_huizong = pd.DataFrame()\n",
    "APS_am1_out_huizong= APS_am1_1.merge(APS_am1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_am1_out_huizong= APS_am1_out_huizong.merge(APS_am1_d, on = 'CUST_NO', how = 'left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.3 转入金额分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:02:32.112804Z",
     "iopub.status.busy": "2023-11-07T04:02:32.112593Z",
     "iopub.status.idle": "2023-11-07T04:03:48.309980Z",
     "shell.execute_reply": "2023-11-07T04:03:48.309312Z",
     "shell.execute_reply.started": "2023-11-07T04:02:32.112779Z"
    }
   },
   "outputs": [],
   "source": [
    "#根据【客户号】统计\n",
    "APS_am2 = tr_APS[tr_APS['APSDTRAMT']>0].copy(deep=True)\n",
    "APS_am2_1 = make_gp(APS_am2,  ['CUST_NO'], ['APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am2_1.columns = rename_df(APS_am2_1,'CUST_NO',\"APS_inamt\")\n",
    "#根据【客户号，月】统计\n",
    "APS_am2_m = make_gp(APS_am2,  ['CUST_NO'], ['APSDTRDAT_TM_month', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am2_m.columns = rename_df(APS_am2_m,'CUST_NO',\"APS_inmamt\")\n",
    "#根据【客户号，天】统计\n",
    "APS_am2_d= make_gp(APS_am2,  ['CUST_NO'], ['APSDTRDAT_TM_day', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am2_d.columns = rename_df(APS_am2_d,'CUST_NO',\"APS_indamt\")\n",
    "#汇总整理\n",
    "APS_am2_in_huizong = pd.DataFrame()\n",
    "APS_am2_in_huizong= APS_am2_1.merge(APS_am2_m, on = 'CUST_NO', how = 'left')\n",
    "APS_am2_in_huizong= APS_am2_in_huizong.merge(APS_am2_d, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.4 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:03:48.311166Z",
     "iopub.status.busy": "2023-11-07T04:03:48.310956Z",
     "iopub.status.idle": "2023-11-07T04:04:05.376531Z",
     "shell.execute_reply": "2023-11-07T04:04:05.375864Z",
     "shell.execute_reply.started": "2023-11-07T04:03:48.311142Z"
    }
   },
   "outputs": [],
   "source": [
    "#根据【客户号】统计交易代码\n",
    "APS_cod1 = tr_APS.copy(deep=True)\n",
    "APS_cod1_1 = make_gp(APS_cod1,  ['CUST_NO'], ['APSDTRCOD'],['count', 'nunique'])\n",
    "APS_cod1_1.columns = rename_df(APS_cod1_1,'CUST_NO',\"APS_cod\")\n",
    "#根据【客户号，月】统计\n",
    "APS_cod1_m = make_gp(APS_cod1,  ['CUST_NO'], ['APSDTRDAT_TM_month','APSDTRCOD'],['count', 'nunique'])\n",
    "APS_cod1_m.columns = rename_df(APS_cod1_m,'CUST_NO',\"APS_mcod\")\n",
    "#根据【客户号，天】统计\n",
    "APS_cod1_d = make_gp(APS_cod1,  ['CUST_NO'], ['APSDTRDAT_TM_day','APSDTRCOD'],['count', 'nunique'])\n",
    "APS_cod1_d.columns = rename_df(APS_cod1_d,'CUST_NO',\"APS_dcod\")\n",
    "#汇总整理\n",
    "APS_cod1_huizong = pd.DataFrame()\n",
    "APS_cod1_huizong= APS_cod1_1.merge(APS_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_cod1_huizong= APS_cod1_huizong.merge(APS_cod1_d, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.5 交易渠道分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:04:05.377713Z",
     "iopub.status.busy": "2023-11-07T04:04:05.377508Z",
     "iopub.status.idle": "2023-11-07T04:04:21.953303Z",
     "shell.execute_reply": "2023-11-07T04:04:21.952628Z",
     "shell.execute_reply.started": "2023-11-07T04:04:05.377689Z"
    }
   },
   "outputs": [],
   "source": [
    "#根据【客户号】统计\n",
    "APS_chl1 = tr_APS.copy(deep=True)\n",
    "APS_chl1_1 = make_gp(APS_chl1,  ['CUST_NO'], ['APSDTRCHL'],['count', 'nunique'])\n",
    "APS_chl1_1.columns = rename_df(APS_chl1_1,'CUST_NO',\"APS_chl\")\n",
    "#根据【客户号，月】统计\n",
    "APS_chl1_m = make_gp(APS_chl1,  ['CUST_NO'], ['APSDTRDAT_TM_month','APSDTRCHL'],['count', 'nunique'])\n",
    "APS_chl1_m.columns = rename_df(APS_chl1_m,'CUST_NO',\"APS_mchl\")\n",
    "#根据【客户号，天】统计\n",
    "APS_chl1_d = make_gp(APS_chl1,  ['CUST_NO'], ['APSDTRDAT_TM_day','APSDTRCHL'],['count', 'nunique'])\n",
    "APS_chl1_d.columns = rename_df(APS_chl1_d,'CUST_NO',\"APS_dchl\")\n",
    "#汇总整理\n",
    "APS_chl1_huizong = pd.DataFrame()\n",
    "APS_chl1_huizong= APS_chl1_1.merge(APS_chl1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_chl1_huizong= APS_chl1_huizong.merge(APS_chl1_d, on = 'CUST_NO', how = 'left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.6 交易对手账号分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:04:21.954484Z",
     "iopub.status.busy": "2023-11-07T04:04:21.954272Z",
     "iopub.status.idle": "2023-11-07T04:04:42.819955Z",
     "shell.execute_reply": "2023-11-07T04:04:42.819291Z",
     "shell.execute_reply.started": "2023-11-07T04:04:21.954460Z"
    }
   },
   "outputs": [],
   "source": [
    "#根据【客户号】统计\n",
    "APS_acc1 = tr_APS.copy(deep=True)\n",
    "APS_acc1_1 = make_gp(APS_acc1,  ['CUST_NO'], ['APSDCPTPRDNO'],['count', 'nunique'])\n",
    "APS_acc1_1.columns = rename_df(APS_acc1_1,'CUST_NO',\"APS_acc\")\n",
    "#根据【客户号，月】统计\n",
    "APS_acc1_m = make_gp(APS_acc1,  ['CUST_NO'], ['APSDTRDAT_TM_month','APSDCPTPRDNO'],['count', 'nunique'])\n",
    "APS_acc1_m.columns = rename_df(APS_acc1_m,'CUST_NO',\"APS_macc\")\n",
    "#根据【客户号，天】统计\n",
    "APS_acc1_d = make_gp(APS_acc1,  ['CUST_NO'], ['APSDTRDAT_TM_day','APSDCPTPRDNO'],['count', 'nunique'])\n",
    "APS_acc1_d.columns = rename_df(APS_acc1_d,'CUST_NO',\"APS_dacc\")\n",
    "#汇总整理\n",
    "APS_acc1_huizong = pd.DataFrame()\n",
    "APS_acc1_huizong= APS_acc1_1.merge(APS_acc1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_acc1_huizong= APS_acc1_huizong.merge(APS_acc1_d, on = 'CUST_NO', how = 'left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.7 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:04:42.821172Z",
     "iopub.status.busy": "2023-11-07T04:04:42.820961Z",
     "iopub.status.idle": "2023-11-07T04:04:43.730942Z",
     "shell.execute_reply": "2023-11-07T04:04:43.730312Z",
     "shell.execute_reply.started": "2023-11-07T04:04:42.821148Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41956, 147)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "APS_feature=pd.DataFrame(tr_APS[\"CUST_NO\"].drop_duplicates())\n",
    "APS_feature=APS_feature.merge(APS_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature=APS_feature.merge(APS_am1_out_huizong, on = 'CUST_NO', how = 'left')  \n",
    "APS_feature=APS_feature.merge(APS_am2_in_huizong, on = 'CUST_NO', how = 'left')  \n",
    "APS_feature=APS_feature.merge(APS_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature=APS_feature.merge(APS_chl1_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature=APS_feature.merge(APS_acc1_huizong, on = 'CUST_NO', how = 'left') \n",
    "\n",
    "# 剔除空值率高\n",
    "null_percentage = APS_feature.isnull().mean()\n",
    "threshold = 0.95\n",
    "null_high_col_aps1 = []\n",
    "null_high_col_aps1 = null_percentage[null_percentage>threshold].index.tolist()\n",
    "feature_name_APS = [i for i in APS_feature.columns if i not in null_high_col_aps1]\n",
    "APS_feature = APS_feature[feature_name_APS].reset_index(drop=True)\n",
    "APS_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.8 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:04:43.732081Z",
     "iopub.status.busy": "2023-11-07T04:04:43.731880Z",
     "iopub.status.idle": "2023-11-07T04:06:38.802100Z",
     "shell.execute_reply": "2023-11-07T04:06:38.801386Z",
     "shell.execute_reply.started": "2023-11-07T04:04:43.732057Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "APS_2 = APS_1_time_diff.copy(deep=True) \n",
    "\n",
    "APS_2_2 = APS_2.loc[APS_2['time_diff']>42]\n",
    "APS_2_2 = make_gp(APS_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_2.columns = ['CUST_NO'] + [f +'_APS'+ '_last14' for f in APS_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_2_4 = APS_2.loc[APS_2['time_diff']>28]\n",
    "APS_2_4 = make_gp(APS_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_4.columns = ['CUST_NO'] + [f +'_APS' + '_last28' for f in APS_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_2_6 = APS_2.loc[APS_2['time_diff']>14]\n",
    "APS_2_6 = make_gp(APS_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_6.columns = ['CUST_NO'] + [f +'_APS'+ '_last42' for f in APS_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_2_8 = APS_2.loc[APS_2['time_diff']>0]\n",
    "APS_2_8 = make_gp(APS_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_8.columns = ['CUST_NO'] + [f +'_APS'+ '_last56' for f in APS_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "APS_2_huizong=pd.DataFrame()\n",
    "APS_2_huizong=APS_2_2.merge(APS_2_4, on = 'CUST_NO', how = 'left')\n",
    "APS_2_huizong=APS_2_huizong.merge(APS_2_6, on = 'CUST_NO', how = 'left')\n",
    "APS_2_huizong=APS_2_huizong.merge(APS_2_8, on = 'CUST_NO', how = 'left')\n",
    "for d in [14, 28,42,56]:\n",
    "        APS_2_huizong[f'times_APS_rate_last{d}'] = APS_2_huizong[f'times_sum_APS_last{d}'] / APS_1_huizong[f'APS_times_sum']\n",
    "        APS_2_huizong[f'time_APS_diff_rate_last{d}'] = APS_2_huizong[f'time_diff_sum_APS_last{d}'] / APS_1_huizong[f'APS_time_diff_sum']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.9 转出金额滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:06:38.803290Z",
     "iopub.status.busy": "2023-11-07T04:06:38.803088Z",
     "iopub.status.idle": "2023-11-07T04:06:51.963901Z",
     "shell.execute_reply": "2023-11-07T04:06:51.963198Z",
     "shell.execute_reply.started": "2023-11-07T04:06:38.803266Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "APS_3 = tr_APS[tr_APS['APSDTRAMT']<0].copy(deep=True)\n",
    "APS_3.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "APS_3_2 = make_gp(APS_3[(APS_3['time_diff']>42)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_2.columns = ['CUST_NO'] + [f +'_APSout'+ '_last14' for f in APS_3_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_3_4 = make_gp(APS_3[(APS_3['time_diff']>28)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_4.columns = ['CUST_NO'] + [f +'_APSout'+ '_last28' for f in APS_3_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_3_6 = make_gp(APS_3[(APS_3['time_diff']>14)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_6.columns = ['CUST_NO'] + [f +'_APSout'+ '_last42' for f in APS_3_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_3_8 = make_gp(APS_3[(APS_3['time_diff']>0)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_8.columns = ['CUST_NO'] + [f +'_APSout'+ '_last56' for f in APS_3_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "APS_3_huizong=pd.DataFrame()\n",
    "APS_3_huizong=APS_3_2.merge(APS_3_4, on = 'CUST_NO', how = 'left')\n",
    "APS_3_huizong=APS_3_huizong.merge(APS_3_6, on = 'CUST_NO', how = 'left')\n",
    "APS_3_huizong=APS_3_huizong.merge(APS_3_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "#滑窗后的交易金额占比：（总—分）/总\n",
    "for col2 in ['APSDTRAMT', ]:\n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last28'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last28'])/APS_3_huizong[f'{col2}_sum_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last28'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last28'])/APS_3_huizong[f'{col2}_mean_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last28']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last28'])/APS_3_huizong[f'{col2}_max_APSout_last28']                                                                                                              \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last28']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last28'])/APS_3_huizong[f'{col2}_min_APSout_last28']                                                                                                    \n",
    "    \n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last28'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last28'])/APS_3_huizong[f'{col2}_sum_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last28'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last28'])/APS_3_huizong[f'{col2}_mean_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last28']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last28'])/APS_3_huizong[f'{col2}_max_APSout_last28']                                                                                                     \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last28']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last28'])/APS_3_huizong[f'{col2}_min_APSout_last28']\n",
    "    \n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last42'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last42'])/APS_3_huizong[f'{col2}_sum_APSout_last42']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last42'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last42'])/APS_3_huizong[f'{col2}_mean_APSout_last42']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last42']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last42'])/APS_3_huizong[f'{col2}_max_APSout_last42']                                                                                                         \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last42']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last42'])/APS_3_huizong[f'{col2}_min_APSout_last42']\n",
    "    \n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last56'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last56'])/APS_3_huizong[f'{col2}_sum_APSout_last56']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last56'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last56'])/ APS_3_huizong[f'{col2}_mean_APSout_last56']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last56']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last56'])/APS_3_huizong[f'{col2}_max_APSout_last56']                                                                                           \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last56']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last56'])/APS_3_huizong[f'{col2}_min_APSout_last56']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.10 转入金额滑窗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:06:51.965078Z",
     "iopub.status.busy": "2023-11-07T04:06:51.964874Z",
     "iopub.status.idle": "2023-11-07T04:07:00.082881Z",
     "shell.execute_reply": "2023-11-07T04:07:00.082162Z",
     "shell.execute_reply.started": "2023-11-07T04:06:51.965053Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "APS_4 = tr_APS[tr_APS['APSDTRAMT']>0].copy(deep=True)\n",
    "APS_4.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "APS_4_2 = make_gp(APS_4[(APS_4['time_diff']>42)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_2.columns = ['CUST_NO'] + [f +'_APSin'+ '_last14' for f in APS_4_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_4_4 = make_gp(APS_4[(APS_4['time_diff']>28)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_4.columns = ['CUST_NO'] + [f +'_APSin'+ '_last28' for f in APS_4_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_4_6 = make_gp(APS_4[(APS_4['time_diff']>14)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_6.columns = ['CUST_NO'] + [f +'_APSin'+ '_last42' for f in APS_4_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_4_8 = make_gp(APS_4[(APS_4['time_diff']>0)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_8.columns = ['CUST_NO'] + [f +'_APSin'+ '_last56' for f in APS_4_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "APS_4_huizong=pd.DataFrame()\n",
    "APS_4_huizong=APS_4_2.merge(APS_4_4, on = 'CUST_NO', how = 'left')\n",
    "APS_4_huizong=APS_4_huizong.merge(APS_4_6, on = 'CUST_NO', how = 'left')\n",
    "APS_4_huizong=APS_4_huizong.merge(APS_4_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "#滑窗后的交易金额占比：（总—分）/总\n",
    "for col2 in ['APSDTRAMT', ]:\n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last28'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last28'])/APS_4_huizong[f'{col2}_sum_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last28'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last28'])/APS_4_huizong[f'{col2}_mean_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last28']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last28'])/APS_4_huizong[f'{col2}_max_APSin_last28']                                                                                                           \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last28']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last28'])/APS_4_huizong[f'{col2}_min_APSin_last28']                                                                                                          \n",
    "    \n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last28'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last28'])/APS_4_huizong[f'{col2}_sum_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last28'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last28'])/APS_4_huizong[f'{col2}_mean_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last28']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last28'])/APS_4_huizong[f'{col2}_max_APSin_last28']                                                                                                      \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last28']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last28'])/APS_4_huizong[f'{col2}_min_APSin_last28']\n",
    "    \n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last42'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last42'])/APS_4_huizong[f'{col2}_sum_APSin_last42']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last42'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last42'])/APS_4_huizong[f'{col2}_mean_APSin_last42']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last42']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last42'])/APS_4_huizong[f'{col2}_max_APSin_last42']                                                                                                       \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last42']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last42'])/APS_4_huizong[f'{col2}_min_APSin_last42']\n",
    "    \n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last56'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last56'])/APS_4_huizong[f'{col2}_sum_APSin_last56']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last56'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last56'])/APS_4_huizong[f'{col2}_mean_APSin_last56']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last56']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last56'])/APS_4_huizong[f'{col2}_max_APSin_last56']                                                                                               \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last56']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last56'])/APS_4_huizong[f'{col2}_min_APSin_last56']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.11 汇总所有的滑窗结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:00.083977Z",
     "iopub.status.busy": "2023-11-07T04:07:00.083787Z",
     "iopub.status.idle": "2023-11-07T04:07:01.172332Z",
     "shell.execute_reply": "2023-11-07T04:07:01.171737Z",
     "shell.execute_reply.started": "2023-11-07T04:07:00.083954Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41956, 267)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "APS_huachuang_huizong=pd.DataFrame()\n",
    "APS_huachuang_huizong=APS_2_huizong.merge(APS_3_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_huachuang_huizong=APS_huachuang_huizong.merge(APS_4_huizong, on = 'CUST_NO', how = 'left') \n",
    "APS_feature=APS_feature.merge(APS_huachuang_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.12 保存APS_feature特征结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:01.173526Z",
     "iopub.status.busy": "2023-11-07T04:07:01.173313Z",
     "iopub.status.idle": "2023-11-07T04:07:01.481529Z",
     "shell.execute_reply": "2023-11-07T04:07:01.480950Z",
     "shell.execute_reply.started": "2023-11-07T04:07:01.173503Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_5_APS.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(APS_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T09:29:34.271738Z",
     "iopub.status.busy": "2023-10-23T09:29:34.271457Z",
     "iopub.status.idle": "2023-10-23T09:29:34.274671Z",
     "shell.execute_reply": "2023-10-23T09:29:34.274115Z",
     "shell.execute_reply.started": "2023-10-23T09:29:34.271710Z"
    },
    "tags": []
   },
   "source": [
    "# 4 分组统计特征补充"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 函数定义"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:01.482722Z",
     "iopub.status.busy": "2023-11-07T04:07:01.482518Z",
     "iopub.status.idle": "2023-11-07T04:07:01.493767Z",
     "shell.execute_reply": "2023-11-07T04:07:01.493263Z",
     "shell.execute_reply.started": "2023-11-07T04:07:01.482697Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def common_process(df, id, op_cols, pks=None, col_type=None, ext=''):\n",
    "    def kurt(arr):\n",
    "        return arr.kurt()\n",
    "    # 统计sliding_window\n",
    "    # stat_name = ['mean','max','min','median','std','sum','skew','count','nunique','last', 'ptp','kurt']\n",
    "    # stat_features = ['mean','max','min','median','std','sum','skew','count','nunique','last',np.ptp, kurt]\n",
    "    stat_name = ['mean','max','min','median','std','sum','count']\n",
    "    stat_features = ['mean','max','min','median','std','sum','count']\n",
    "    if col_type != 'num':\n",
    "        stat_name = ['nunique','count']\n",
    "        stat_features = ['nunique','count'] # count 容易重复\n",
    "    agg_stat = {}\n",
    "    # 存放统计列，为后面行转列准备\n",
    "    temp_columns=[]\n",
    "    for i in op_cols:\n",
    "        agg_stat[i] = stat_features\n",
    "        temp_columns.extend([i + '_' + f + '_' + ext for f in stat_name])\n",
    "\n",
    "    ids = [id]\n",
    "    if pks is not None:\n",
    "        ids.extend(pks)\n",
    "    temp = df.groupby(ids).agg(agg_stat)\n",
    "    temp.columns = [f[0]+ '_' + f[1]  + \"_\" + str(ext) for f in temp.columns]\n",
    "    # print(temp.columns)\n",
    "    temp = temp.reset_index(drop = False)\n",
    "\n",
    "    if pks is not None:\n",
    "        # 将主键列合并成一列，后面扩展为列名\n",
    "        temp['combine'] = temp[pks[0]]\n",
    "        for i in pks[1:]:\n",
    "            temp['combine'] = temp['combine'].astype(str) +\"_\"+ temp[i].astype(str)\n",
    "        # 行转列，类别转列名特征\n",
    "        # id,扩展列，统计量的列\n",
    "#         print(temp_columns)\n",
    "#         print(temp.head())\n",
    "        temp=temp.set_index([id,'combine'])[temp_columns].unstack()\n",
    "        temp.columns = [str(f[0])+ '_' + str(f[1]) for f in temp.columns]\n",
    "        temp=temp.reset_index()\n",
    "    return temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:01.494736Z",
     "iopub.status.busy": "2023-11-07T04:07:01.494558Z",
     "iopub.status.idle": "2023-11-07T04:07:01.498303Z",
     "shell.execute_reply": "2023-11-07T04:07:01.497744Z",
     "shell.execute_reply.started": "2023-11-07T04:07:01.494713Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#剔除缺失值\n",
    "def res(df):\n",
    "    res = stat_df(df)\n",
    "    res = res[res['缺失值占比'] > 80]\n",
    "    missing = np.array(res['特征'])\n",
    "    print(np.shape(missing))\n",
    "    return missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:01.499187Z",
     "iopub.status.busy": "2023-11-07T04:07:01.499005Z",
     "iopub.status.idle": "2023-11-07T04:07:01.502489Z",
     "shell.execute_reply": "2023-11-07T04:07:01.502040Z",
     "shell.execute_reply.started": "2023-11-07T04:07:01.499166Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#最终特征确认\n",
    "def feature_qr(df,missing):\n",
    "    feature_name = [i for i in df.columns if i not in missing]\n",
    "    df = df[feature_name].reset_index(drop=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:01.503417Z",
     "iopub.status.busy": "2023-11-07T04:07:01.503239Z",
     "iopub.status.idle": "2023-11-07T04:07:51.711053Z",
     "shell.execute_reply": "2023-11-07T04:07:51.710542Z",
     "shell.execute_reply.started": "2023-11-07T04:07:01.503396Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1673,)\n",
      "(2975,)\n",
      "(138,)\n",
      "(478,)\n",
      "(850,)\n",
      "(138,)\n",
      "(478,)\n",
      "(850,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(41217, 253)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1 掌银金融性流水表\n",
    "MBANK_TRNFLW_data['TFT_DTE_week'] = MBANK_TRNFLW_data['TFT_DTE_TIME'].dt.dayofweek\n",
    "col=['TFT_DTE_TIME','TFT_DTE_year','TFT_DTE_day']\n",
    "for i in col:\n",
    "    del MBANK_TRNFLW_data[i]\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易金额\n",
    "tr_mamt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_TRNAMT'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='num',ext='mbank_trn_mamt')\n",
    "missing=res(tr_mamt_TRNFLW)\n",
    "tr_mamt_TRNFLW=feature_qr(tr_mamt_TRNFLW,missing)\n",
    "tr_mamt_TRNFLW.shape\n",
    "\n",
    "# 根据账号和 标准业务代码按月分组统计交易金额\n",
    "tr_wamt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_TRNAMT'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='num',ext='mbank_trn_wamt')\n",
    "missing=res(tr_wamt_TRNFLW)\n",
    "tr_wamt_TRNFLW=feature_qr(tr_wamt_TRNFLW,missing)\n",
    "tr_wamt_TRNFLW.shape\n",
    "\n",
    "# 根据账号和 标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CSTACC'],['TFT_STDBSNCOD'],col_type='',ext='mbank_trn_ycnt')\n",
    "missing=res(tr_ycnt_TRNFLW)\n",
    "tr_ycnt_TRNFLW=feature_qr(tr_ycnt_TRNFLW,missing)\n",
    "tr_ycnt_TRNFLW.shape\n",
    "\n",
    "# 根据账号和 标准业务代码按月分组统计交易频数\n",
    "tr_mcnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CSTACC'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_mcnt')\n",
    "missing=res(tr_mcnt_TRNFLW)\n",
    "tr_mcnt_TRNFLW=feature_qr(tr_mcnt_TRNFLW,missing)\n",
    "tr_mcnt_TRNFLW.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CSTACC'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_wcnt')\n",
    "missing=res(tr_wcnt_TRNFLW)\n",
    "tr_wcnt_TRNFLW=feature_qr(tr_wcnt_TRNFLW,missing)\n",
    "tr_wcnt_TRNFLW.shape\n",
    "\n",
    "# 根据账号和 标准业务代码按年分组统计交易频数\n",
    "tr_yacccnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CRPACC'],['TFT_STDBSNCOD'],col_type='',ext='mbank_trn_yacccnt')\n",
    "missing=res(tr_yacccnt_TRNFLW)\n",
    "tr_wcnt_TRNFLW=feature_qr(tr_yacccnt_TRNFLW,missing)\n",
    "tr_yacccnt_TRNFLW.shape\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易对手\n",
    "tr_macccnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CRPACC'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_macccnt')\n",
    "missing=res(tr_macccnt_TRNFLW)\n",
    "tr_macccnt_TRNFLW=feature_qr(tr_macccnt_TRNFLW,missing)\n",
    "tr_macccnt_TRNFLW.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易对手\n",
    "tr_wacccnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CRPACC'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_wacccnt')\n",
    "missing=res(tr_wacccnt_TRNFLW)\n",
    "tr_wacccnt_TRNFLW=feature_qr(tr_wacccnt_TRNFLW,missing)\n",
    "tr_wacccnt_TRNFLW.shape\n",
    "\n",
    "#合并\n",
    "TRNFLW_feature1=pd.DataFrame(MBANK_TRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_mamt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_wamt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_ycnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_mcnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_wcnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_yacccnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_macccnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_wacccnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:07:51.712156Z",
     "iopub.status.busy": "2023-11-07T04:07:51.711957Z",
     "iopub.status.idle": "2023-11-07T04:08:46.887461Z",
     "shell.execute_reply": "2023-11-07T04:08:46.886962Z",
     "shell.execute_reply.started": "2023-11-07T04:07:51.712132Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(438,)\n",
      "(1488,)\n",
      "(2628,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(41882, 99)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2 掌银非金融\n",
    "MBANK_QRYTRNFLW_data['TFT_DTE_week'] = MBANK_QRYTRNFLW_data['TFT_DTE_TIME'].dt.dayofweek\n",
    "col=['TFT_DTE_TIME','TFT_DTE_year','TFT_DTE_day']\n",
    "for i in col:\n",
    "    del MBANK_QRYTRNFLW_data[i]\n",
    "\n",
    "# 根据账号和 标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_QRYTRNFLW= common_process(MBANK_QRYTRNFLW_data,'CUST_NO',['CUST_NO'],['TFT_STDBSNCOD'],col_type='',ext='mbank_qry_ycnt')\n",
    "missing=res(tr_ycnt_QRYTRNFLW)\n",
    "tr_ycnt_QRYTRNFLW=feature_qr(tr_ycnt_QRYTRNFLW,missing)\n",
    "tr_ycnt_QRYTRNFLW.shape\n",
    "#根据客户号和标准业务代码按月分组统计交易频数\n",
    "tr_mcnt_QRYTRNFLW= common_process(MBANK_QRYTRNFLW_data,'CUST_NO',['CUST_NO'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='',ext='mbank_qry_mcnt')\n",
    "missing=res(tr_mcnt_QRYTRNFLW)\n",
    "tr_mcnt_QRYTRNFLW=feature_qr(tr_mcnt_QRYTRNFLW,missing)\n",
    "tr_mcnt_QRYTRNFLW.shape\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_QRYTRNFLW= common_process(MBANK_QRYTRNFLW_data,'CUST_NO',['CUST_NO'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='',ext='mbank_qry_wcnt')\n",
    "missing=res(tr_wcnt_QRYTRNFLW)\n",
    "tr_wcnt_QRYTRNFLW=feature_qr(tr_wcnt_QRYTRNFLW,missing)\n",
    "tr_wcnt_QRYTRNFLW.shape\n",
    "#合并\n",
    "QRYTRNFLW_feature1=pd.DataFrame(MBANK_QRYTRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "QRYTRNFLW_feature1=QRYTRNFLW_feature1.merge(tr_ycnt_QRYTRNFLW, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature1=QRYTRNFLW_feature1.merge(tr_mcnt_QRYTRNFLW, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature1=QRYTRNFLW_feature1.merge(tr_wcnt_QRYTRNFLW, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:46.888593Z",
     "iopub.status.busy": "2023-11-07T04:08:46.888401Z",
     "iopub.status.idle": "2023-11-07T04:08:48.561194Z",
     "shell.execute_reply": "2023-11-07T04:08:48.560692Z",
     "shell.execute_reply.started": "2023-11-07T04:08:46.888569Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(64,)\n",
      "(224,)\n",
      "(386,)\n",
      "(18,)\n",
      "(64,)\n",
      "(110,)\n",
      "(18,)\n",
      "(64,)\n",
      "(110,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(493, 131)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#3 网银金融表\n",
    "EBANK_CSTLOG_data['ADDFIELDDATE_week'] = EBANK_CSTLOG_data['ADDFIELDDATE'].dt.dayofweek\n",
    "col=['ADDFIELDDATE','ADDFIELDDATE_year','ADDFIELDDATE_day']\n",
    "for i in col:\n",
    "    del EBANK_CSTLOG_data[i]\n",
    "#根据客户号和标准业务代码按年分组统计交易金额\n",
    "tr_yamt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TRNAMT'],['BSNCODE'],col_type='num',ext='ebank_trn_yamt')\n",
    "missing=res(tr_yamt_CSTLOG)\n",
    "tr_yamt_CSTLOG=feature_qr(tr_yamt_CSTLOG,missing)\n",
    "tr_yamt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易金额\n",
    "tr_mamt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TRNAMT'],['ADDFIELDDATE_month','BSNCODE'],col_type='num',ext='ebank_trn_mamt')\n",
    "missing=res(tr_mamt_CSTLOG)\n",
    "tr_mamt_CSTLOG=feature_qr(tr_mamt_CSTLOG,missing)\n",
    "tr_mamt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易金额\n",
    "tr_wamt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TRNAMT'],['ADDFIELDDATE_week','BSNCODE'],col_type='num',ext='ebank_trn_wamt')\n",
    "missing=res(tr_wamt_CSTLOG)\n",
    "tr_wamt_CSTLOG=feature_qr(tr_wamt_CSTLOG,missing)\n",
    "tr_wamt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['FRMACCTNO'],['BSNCODE'],col_type='',ext='ebank_trn_ycnt')\n",
    "missing=res(tr_ycnt_CSTLOG)\n",
    "tr_ycnt_CSTLOG=feature_qr(tr_ycnt_CSTLOG,missing)\n",
    "tr_ycnt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易频数\n",
    "tr_mcnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['FRMACCTNO'],['ADDFIELDDATE_month','BSNCODE'],col_type='',ext='ebank_trn_mcnt')\n",
    "missing=res(tr_mcnt_CSTLOG)\n",
    "tr_mcnt_CSTLOG=feature_qr(tr_mcnt_CSTLOG,missing)\n",
    "tr_mcnt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['FRMACCTNO'],['ADDFIELDDATE_week','BSNCODE'],col_type='',ext='ebank_trn_wcnt')\n",
    "missing=res(tr_wcnt_CSTLOG)\n",
    "tr_wcnt_CSTLOG=feature_qr(tr_wcnt_CSTLOG,missing)\n",
    "tr_wcnt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易对手\n",
    "tr_yacccnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TOACCTNO'],['BSNCODE'],col_type='',ext='ebank_trn_yacccnt')\n",
    "missing=res(tr_yacccnt_CSTLOG)\n",
    "tr_yacccnt_CSTLOG=feature_qr(tr_yacccnt_CSTLOG,missing)\n",
    "tr_yacccnt_CSTLOG.shape\n",
    "#根据客户号和标准业务代码按月分组统计交易对手\n",
    "tr_macccnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TOACCTNO'],['ADDFIELDDATE_month','BSNCODE'],col_type='',ext='ebank_trn_macccnt')\n",
    "missing=res(tr_macccnt_CSTLOG)\n",
    "tr_macccnt_CSTLOG=feature_qr(tr_macccnt_CSTLOG,missing)\n",
    "tr_macccnt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易对手\n",
    "tr_wacccnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TOACCTNO'],['ADDFIELDDATE_week','BSNCODE'],col_type='',ext='ebank_trn_wacccnt')\n",
    "missing=res(tr_wacccnt_CSTLOG)\n",
    "tr_wacccnt_CSTLOG=feature_qr(tr_wacccnt_CSTLOG,missing)\n",
    "tr_wacccnt_CSTLOG.shape\n",
    "\n",
    "# 合并\n",
    "CSTLOG_feature1=pd.DataFrame(EBANK_CSTLOG_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_yamt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_mamt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_wamt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_ycnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_mcnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_wcnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_yacccnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_macccnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_wacccnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:48.562277Z",
     "iopub.status.busy": "2023-11-07T04:08:48.562086Z",
     "iopub.status.idle": "2023-11-07T04:09:02.879661Z",
     "shell.execute_reply": "2023-11-07T04:09:02.879152Z",
     "shell.execute_reply.started": "2023-11-07T04:08:48.562252Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(374,)\n",
      "(946,)\n",
      "(1526,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1083, 385)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#4 网银非金融表\n",
    "EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME_week'] = EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'].dt.dayofweek\n",
    "col=['CLQ_DTE_TIME','CLQ_DTE_year','CLQ_DTE_day']\n",
    "for i in col:\n",
    "    del EBANK_CSTLOGQUERY_data[i]\n",
    "\n",
    "# 根据客户号和标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_CSTLOGQUERY= common_process(EBANK_CSTLOGQUERY_data,'CUST_NO',['CUST_NO'],['CLQ_BSNCOD'],col_type='',ext='ebank_qry_ycnt')\n",
    "missing=res(tr_ycnt_CSTLOGQUERY)\n",
    "tr_ycnt_CSTLOGQUERY=feature_qr(tr_ycnt_CSTLOGQUERY,missing)\n",
    "tr_ycnt_CSTLOGQUERY.shape\n",
    "\n",
    "#根据客户号和标准业务代码月年分组统计交易频数\n",
    "tr_mcnt_CSTLOGQUERY= common_process(EBANK_CSTLOGQUERY_data,'CUST_NO',['CUST_NO'],['CLQ_DTE_month','CLQ_BSNCOD'],col_type='',ext='ebank_qry_mcnt')\n",
    "missing=res(tr_mcnt_CSTLOGQUERY)\n",
    "tr_mcnt_CSTLOGQUERY=feature_qr(tr_mcnt_CSTLOGQUERY,missing)\n",
    "tr_mcnt_CSTLOGQUERY.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_CSTLOGQUERY= common_process(EBANK_CSTLOGQUERY_data,'CUST_NO',['CUST_NO'],['CLQ_DTE_TIME_week','CLQ_BSNCOD'],col_type='',ext='ebank_qry_wcnt')\n",
    "missing=res(tr_wcnt_CSTLOGQUERY)\n",
    "tr_wcnt_CSTLOGQUERY=feature_qr(tr_wcnt_CSTLOGQUERY,missing)\n",
    "tr_wcnt_CSTLOGQUERY.shape\n",
    "\n",
    "#合并\n",
    "CSTLOGQUERY_feature1=pd.DataFrame(EBANK_CSTLOGQUERY_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOGQUERY_feature1=CSTLOGQUERY_feature1.merge(tr_ycnt_CSTLOGQUERY, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature1=CSTLOGQUERY_feature1.merge(tr_mcnt_CSTLOGQUERY, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature1=CSTLOGQUERY_feature1.merge(tr_wcnt_CSTLOGQUERY, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:09:02.880746Z",
     "iopub.status.busy": "2023-11-07T04:09:02.880556Z",
     "iopub.status.idle": "2023-11-07T04:09:07.931903Z",
     "shell.execute_reply": "2023-11-07T04:09:07.931396Z",
     "shell.execute_reply.started": "2023-11-07T04:09:02.880722Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(652,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(42074, 213)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_1234=pd.DataFrame(target_data[\"CUST_NO\"].drop_duplicates())\n",
    "feature_1234=feature_1234.merge(TRNFLW_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234=feature_1234.merge(QRYTRNFLW_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234=feature_1234.merge(CSTLOG_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234=feature_1234.merge(CSTLOGQUERY_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234.shape\n",
    "missing=res(feature_1234)\n",
    "feature_1234=feature_qr(feature_1234,missing)\n",
    "feature_1234.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 保存特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:09:07.933005Z",
     "iopub.status.busy": "2023-11-07T04:09:07.932809Z",
     "iopub.status.idle": "2023-11-07T04:09:08.206690Z",
     "shell.execute_reply": "2023-11-07T04:09:08.206097Z",
     "shell.execute_reply.started": "2023-11-07T04:09:07.932982Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/A_1234_hebing.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(feature_1234, file)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  },
  "toc-autonumbering": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
